Re: Retrieving Trace Files for Oracle on AWS

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 14 Apr 2017 06:36:48 -0400

Have you tried ADRCI utility?


On 04/13/2017 04:42 PM, Mohammed Bhatti wrote:

Hi Vijay,

Thanks for your input. I tried that query and I was unable to locate the trace file. It's frustrating.

I checked background_dump_dest and it point to /rdsdbdata/log/diag/rdbms/acme_a/ACME/trace.

I then run this query:
SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = 649;

Which shows me that the file generated is in /rdsdbdata/log/diag/rdbms/acme_a/ACME/trace/ACME_ora_13615.trc

I then |exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;

|
|Then run the following:
|
|select filename, mtime
from rdsadmin.tracefile_listing
order by mtime;

|
|But my file doesn't show up.
|
|
|
|I also checked dba_directories and it shows that BDUMP points to /rdsdbdata/log/trace.

|
|So, I'm confused because BDUMP points to a location that is not the same as the tracefile location. I've tried creating a directory to that location but I get insufficient privileges and I'm logged on as awssys.

|
|Anyway, the regular DBA gets back next week, and this wasn't critical, and I'm very new to Oracle on RDS, but it's just bugging me. Why can't I get to my trace files?

|
|Thanks again Vijay.

--
|
|mohammed
|

On Thu, Apr 13, 2017 at 4:06 PM, vijayrsehgal <vijaysehgal21@xxxxxxxxx <mailto:vijaysehgal21@xxxxxxxxx>> wrote:

    HI Mohammed,

    I am also new to AWS and learning, I used the below query to list
    the file and then I logged in AWS console to download the file.
    There are other ways as well, you can see if below query lists
    your file.

    select * from table(rdsadmin.rds_file_util.listdir('BDUMP'));

    Regards,
    Vijay Sehgal

    On Thu, Apr 13, 2017 at 12:39 PM, Mohammed Bhatti
    <mohammed.bhatti1@xxxxxxxxx <mailto:mohammed.bhatti1@xxxxxxxxx>>
    wrote:

        Grrrr....hit send before I finisihed my email, sorry.

        So, I'm the backup dba for a number of databases on running on
        AWS.  The real DBA comes back next week.  Not having done DBA
        work for a while, I'm turning to the list for some help.

        Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
        64bit Production
        CentOS release 6.7 (Final)

        I've turned on 10046 trace for a user and I'm able to identify
        the trace file generated for this user using the following query:

        SELECT p.tracefile
        FROM   v$session s
               JOIN v$process p ON s.paddr = p.addr
        WHERE  s.sid = 20;

        So this lists a directory path
        /rdsdbdata/log/diag/rdbms/.../.../... where my actual file
        is.  I want to view the contents of this file.  I've tried
        stuff from here:
        
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html
        
<http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html>

        e.g. refresh the view on backgroud_dump_dest using this

        |exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;|

        But when I query this

        |rdsadmin.tracefile_listing |

        |I don't see the file. |

        |The file is not in background_dump_dest as shown by the
        location above. |

        |So, how do you retrieve trace files from RDS? |

        |Thanks -- |

        |mohammed |

        On Thu, Apr 13, 2017 at 12:33 PM, Mohammed Bhatti
        <mohammed.bhatti1@xxxxxxxxx
        <mailto:mohammed.bhatti1@xxxxxxxxx>> wrote:

            So, I'm the backup dba for a number of databases on
            running on AWS.  The real DBA comes back next week.  Not
            having done DBA work for a while, I'm turning to the list
            for some help.
            I've turned on 10046 trace for a user and I'm able to
            identify the trace file generated for this user using the
            following query:

--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Other related posts: