7 Replies Latest reply on Mar 10, 2014 8:59 AM by Jyoti Verma -Oracle

    Alter log file


      Hi folks,


      In RAC enviornment the RDBMS/TNSLSNR alert log files is node specific  . suppose i got an error ora 1555 the information will be written to node specific alert log file.

      i now  have doubt  that  if i  add a datafile to tablespace then information will be written

      to the node specific alter log file , if so how can other specific node  alter log file get the information ,please give a clearity on that.



      thanks $ Regards

        • 1. Re: Alter log file
          Vandana B -Oracle



          The instance alert log on the other node will not be update, because once the datafile is added from any specific instance the information is captured at the database level (and not at instance level). You will see an entry only in the alert log of the specific instance from where the command/statement was executed.



          Vandana - Oracle

          • 2. Re: Alter log file

            Alert log file contains the instance specific information and operation performed by specific instance


            so in case of RAC you have at least 2 instances and 1 database


            each instance had separate alert log file


            created tablespace information from either instance would updated on database level so no need to inform the second instance alert log any how.

            • 3. Re: Alter log file
              Ravinder Rathi-Oracle

              Q: If I add a datafile to tablespace then information will be written to the node specific alter log file , if so how can other specific node  alter log file get the information ,please give a clearity on that.


              A: When you add the datafile, information about newly added datafile is added in the the Data Dictionary stored under system tablespace. It will log the commands(for example, "Creation of tablespaces and undo segments" and "Alter statements") executed in the respective instance alert log.


              In RAC since the daatfiles are stored on a shared storage and accessing the same Data Dictionary(SYSTEM Datafile). Other node instances is aware of this information by accessing the Data Dictionary information. If its not able to acess the datafile for some reasons, it will report "ORA-01157" on other Instance alert Log files.



              Let take an interesting example:



              While executing a normal maintenance operation, the dba didn't notice a blank space on the name of the disk-group at the add datafile command. The blank space was at the beginning of the disk-group name as below.


              Node-1 Instance Alert log :



              Sun May 26 11:19:40 2013

              alter tablespace TS_ETL add datafile ' +DG_DATA'  size 8000M

              Sun May 26 11:20:26 2013

              Completed: alter tablespace TS_ETL add datafile ' +DG_DATA'  size 8000M              <<<<<< Command completed successfully and datafile is added



              Now, The command was issued on node 1, and due to the blank space, oracle didn't recognize the disk-group name and created the file at the default location $ORACLE_HOME/dbs (Expected Behavior) putting the name of the datafile as " +DG_DATA " as shown below:


              oracle@hodb009mul /oracle/app/oracle/product/11.2.0/dbhome_1/dbs $ ls -ltr

              -rw-------    1 oracle   oinstall  152051712 May 27 19:07  +DG_DATA



              SQL> select file_name,autoextensible,bytes from dba_data_files where tablespace_name='TS_ETL';


              FILE_NAME                                                                        AUT                         BYTES

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

              +DG_DATA_MUL/prm02/datafile/ts_etl_dad01.275.799091475                           YES                   33554432000

              +DG_DATA_MUL/prm02/datafile/ts_etl_dad01.297.813772339                           NO                    33554432000

              /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/ +DG_DATA                            NO                      152043520           <<<<< added at default location i.e $ORACLE_HOME/dbs

              +DG_DATA_MUL/prm02/datafile/ts_etl_dad01.304.816539639                           NO                    10737418240



              In brief, due to this, the first node(add datafile command logged in Node-1 instance alert log) is accessing the datafile because its in the local location and accessible.

              But it can't be access by another instance(because,  its not on shared location). So, on other instance it will start reporting the below error in alert log:


              Node-2 Instace Alert log :



              Sun May 26 11:21:26 2013

              ORA-01157: cannot identify/lock data file 35   <<<<<<<


              Hope this helps you !!

              • 4. Re: Alter log file

                Look for the value of your DIAGNOSTIC_DEST parameter. In the directory pointed to by this parameter, you should find the following structure:




                In that trace directory will be your alert log. You will have similar on each node of your RAC database, the only thing that changes in the directory path is the instance_name.


                When I set up RAC systems, I like to make sure that DIAGNOSTIC_DEST/diag/rdbms is a mount point shared on all nodes in the cluster. This way, you can view any and all alert logs from any and all nodes in the cluster.



                • 5. Re: Alter log file



                  Thanks for your reply,

                  i agree with you, whenevery any thing is going to happend at database level(like adding datafile) . it is updated in Data Dictionary view which is in  Sytem tablespace, system tablespace is on shared storage, can be accessible by all node in RAC.other node specific alter log file wil not be written.,only local node specific alter log file will have the written information.



                  Thanks & Regards

                  • 6. Re: Alter log file


                    thanks . now i am cleared

                    • 7. Re: Alter log file
                      Jyoti Verma -Oracle

                      Its not necessary that the alert log file from another instance will also get this message. Alert logs only capture the activities happening at the instance level.


                      The other instance will be aware of this change as the control file shared by all the instance will get this update for the newly added datafile.