Re: Retrieving Trace Files for Oracle on AWS

Gus,

You're right. I didn't read Mohammed's email thoroughly enough. Although after reading all the replies, he verifies that his trace file is in fact located in the one fixed directory that serves as both background_dump_dest and user_dump_dest, from which RDS's API-based log fetching is pulling. However, he is using the rdsadmin.* stored procedures to get the files, instead of the AWS CLI. Both should work.

Also, Mohamed noted that there is a directory object named BDUMP that points to /rdsdbdata/log/trace. However, this is just a symlink to the same directory as background_dump_dest.

I tested this on my own 11.2.0.4 instance on RDS, and I have no trouble generating a trace file and seeing it. Furthermore, the user_dump_dest, background_dump_dest and diagnostic_dest are not modifiable on RDS, either at the session or the system level, and all point to the same place (/rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace). I would ask the original poster to reach out to me at my Amazon address (jeremiah_at_amazon.com) and I will assist further with figuring out where things are going awry.

SQL> select value from v$parameter where name = 'user_dump_dest';

VALUE

/rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select * from dual;

D

-

X

SQL> alter session set events '10046 trace name all off';

Session altered.

SQL> select tracefile from v$session s, v$process p where s.paddr = p.addr and s.audsid = sys_context('userenv','sessionid');

TRACEFILE

/rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_ora_9274.trc

$ aws rds describe-db-log-files --db-instance-identifier jworcl1 --filename-contains ora_9274

DESCRIBEDBLOGFILES 1492229825000 trace/ORCL_ora_9274.trc 5600

DESCRIBEDBLOGFILES 1492229825000 trace/ORCL_ora_9274.trm 126

$ aws rds download-db-log-file-portion --db-instance-identifier jworcl1 --log-file-name trace/ORCL_ora_9274.trc

Trace file /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_ora_9274.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /rdsdbbin/oracle

...

PARSING IN CURSOR #46912551160152 len=18 dep=0 uid=35 oct=3 lid=35 tim=1492229812377300 hv=942515969 ad='758ab380' sqlid='a5ks9fhw2v9s1'

select * from dual

END OF STMT ...

SQL> alter session set user_dump_dest='/rdsdbdata/log/trace';

alter session set user_dump_dest='/rdsdbdata/log/trace'

*

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this option

From: "Gus Spier" <gus.spier_at_gmail.com> To: "Jeremiah Cetlin Wilton" <jcwilton93_at_earlham.edu> Cc: "mohammed bhatti1" <mohammed.bhatti1_at_gmail.com>, "gogala mladen" <gogala.mladen_at_gmail.com>, "Oracle Mailing List" <oracle-l_at_freelists.org> Sent: Friday, April 14, 2017 6:52:21 PM Subject: Re: Retrieving Trace Files for Oracle on AWS

I speak under correction, because I am not in any way a skilled RDS Database Administrator.

It appears to me that the underlying problem is the trace file is NOT in the BDUMP directory. Is it possible that this particular trace file is the moral equivalent of the USER_BACKGROUND_DUMP_DEST. Does anyone know if RDS has an Oracle directory object already configured to this "UDUMP" location? If one does not already exist, is there an RDS equivalent to "CREATE DIRECTORY UDUMP ..." Oracle statement?

Regards,

Gus

On Fri, Apr 14, 2017 at 2:41 PM, Jeremiah Cetlin Wilton < jcwilton93_at_earlham.edu > wrote:

Hi Mohammed,

Have you just tried listing the trace files using the AWS CLI or the console?

In the console:

• Select the instance (checkbox)
• Instance Actions -> See Details
• Recent Events and Logs Tab
• Scroll down to find your log, or use the search box
• Click view, watch or download

In the CLI:

aws rds describe-db-log-files --db-instance-identifier myinstance1 aws rds download-db-log-file-portion --db-instance-identifier myinstance1 --log-file-name trace/ORCL_j001_37382.trc --starting-token 0 --output text

You might want to also look at the docs for both of these CLI commands, since they have lots of other useful options.

Let me know if you need any further assistance and the docs don't provide what you need. I work for AWS and can get you help if needed.

Jeremiah

From: "Mohammed Bhatti" < mohammed.bhatti1_at_gmail.com > To: "gogala mladen" < gogala.mladen_at_gmail.com > Cc: "Oracle Mailing List" < oracle-l_at_freelists.org > Sent: Friday, April 14, 2017 7:08:32 AM Subject: Re: Retrieving Trace Files for Oracle on AWS

I have not, but I will look into. Thanks Mladen.

-- 
mohammed 

On Fri, Apr 14, 2017 at 6:36 AM, Mladen Gogala < gogala.mladen_at_gmail.com > wrote: 

BQ_BEGIN

Have you tried ADRCI utility? 


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

BQ_BEGIN

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_at_gmail.com > wrote: 

BQ_BEGIN

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_at_gmail.com > wrote: 

BQ_BEGIN

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 

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_at_gmail.com > wrote: 
BQ_BEGIN

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: 




BQ_END


BQ_END


BQ_END



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

BQ_END



BQ_END



--
http://www.freelists.org/webpage/oracle-l