Access/dump datafile (no alter system/database, e.g. AWS RDS)

[vit\.spinka]

Hi all,

I wonder if there is a way how to get to the datafiles in say Amazon RDS, as there is no filesystem access. And the users cannot have alter system or alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.

Or am I missing somethings, is there a way how to access an arbitrary datafile block?

Thanks for all ideas!

Vit

[angelo]

Hello Spinka,

you should use datapump there to make your import or export schema

here is how to:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

but... no sysdba access, I think dba, maybe

Post by vit\.spinka
Hi all,
I wonder if there is a way how to get to the datafiles in say Amazon RDS,
as there is no filesystem access. And the users cannot have alter system or
alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.
Or am I missing somethings, is there a way how to access an arbitrary datafile block?
Thanks for all ideas!
Vit

[Gus Spier]

I'm not working this Amazon RDS stuff now, but for a while I was exploring
a userid/role called RDSADMIN. There are a number of packages/stored
procedures owned by/available to RDSADMIN. I believe the RDS documentation
will describe them relatively well.

Good luck!

Gus

Post by angelo
Hello Spinka,
you should use datapump there to make your import or export schema
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html
but... no sysdba access, I think dba, maybe

Post by vit\.spinka
Hi all,
I wonder if there is a way how to get to the datafiles in say Amazon RDS,
as there is no filesystem access. And the users cannot have alter system or
alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.
Or am I missing somethings, is there a way how to access an arbitrary datafile block?
Thanks for all ideas!
Vit

[n***@gmail.com]

I don't see how that question makes sense in a Cloud environment. In fact,
I don't really see how it makes sense anywhere outside of a lab/play
environment. What's your use case?

Post by vit\.spinka
Hi all,
I wonder if there is a way how to get to the datafiles in say Amazon RDS,
as there is no filesystem access. And the users cannot have alter system or
alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.
Or am I missing somethings, is there a way how to access an arbitrary datafile block?
Thanks for all ideas!
Vit

["Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)]

We have a similar cloud here. My question is how do I restore a table in dev? Or refresh a testing database from Prod? It’s not the best answer in the world.

Liz

Elizabeth Reen
CPB Database Group Manager

Service Now Group: CPB-ORACLE-DB-SUPPORT


From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of vit.spinka
Sent: Friday, July 27, 2018 6:54 AM
To: Oracle-l
Subject: Access/dump datafile (no alter system/database, e.g. AWS RDS)

Hi all,

I wonder if there is a way how to get to the datafiles in say Amazon RDS, as there is no filesystem access. And the users cannot have alter system or alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.

Or am I missing somethings, is there a way how to access an arbitrary datafile block?

Thanks for all ideas!

Vit

[n***@gmail.com]

Does the automated snapshot capability meet those requirements?
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

Post by "Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)
We have a similar cloud here. My question is how do I
restore a table in dev? Or refresh a testing database from Prod? It’s not
the best answer in the world.
Liz
Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT
*Sent:* Friday, July 27, 2018 6:54 AM
*To:* Oracle-l
*Subject:* Access/dump datafile (no alter system/database, e.g. AWS RDS)
Hi all,
I wonder if there is a way how to get to the datafiles in say Amazon RDS,
as there is no filesystem access. And the users cannot have alter system or
alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.
Or am I missing somethings, is there a way how to access an arbitrary datafile block?
Thanks for all ideas!
Vit

["Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)]

No because the restore is all or nothing. We need to spin up another instance and do an export. This would not be a problem if we had somewhere to put the dump.

Liz

Elizabeth Reen
CPB Database Group Manager
718.248.9930 (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT


From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of ***@gmail.com
Sent: Friday, July 27, 2018 9:50 AM
To: dmarc-***@freelists.org
Cc: ***@vitspinka.cz; ORACLE-L
Subject: Re: Access/dump datafile (no alter system/database, e.g. AWS RDS)

Does the automated snapshot capability meet those requirements? https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html<https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.aws.amazon.com_AmazonRDS_latest_UserGuide_USER-5FPIT.html&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=-iE-mUkYfiC_p7D1IoYXgKouPO7n-A7ZFzvzqCUGbz0&s=5HGhUyabF8EjBorS3Z-rSo8oCJj-L_TQj0vim4dn_8I&e=>

On Fri, Jul 27, 2018 at 2:38 PM Reen, Elizabeth <dmarc-***@freelists.org<mailto:dmarc-***@freelists.org>> wrote:
We have a similar cloud here. My question is how do I restore a table in dev? Or refresh a testing database from Prod? It’s not the best answer in the world.

Liz

Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT


From: oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org> [mailto:oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org>] On Behalf Of vit.spinka
Sent: Friday, July 27, 2018 6:54 AM
To: Oracle-l
Subject: Access/dump datafile (no alter system/database, e.g. AWS RDS)

Hi all,

I wonder if there is a way how to get to the datafiles in say Amazon RDS, as there is no filesystem access. And the users cannot have alter system or alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.

Or am I missing somethings, is there a way how to access an arbitrary datafile block?

Thanks for all ideas!

Vit

[Mladen Gogala]

Hi Elizabeth,

The answer is that you do not. RDS is database as a service. You
populate it using data pump. There is a class of databases for which
that can be used: smaller, in house databases, like an HR database, RMAN
catalog or OEM database. RDS is not the right solution if you have a
100TB OLTP database. For that, you'll have to use an EC2 instance or
on-premises hardware.  Also, be aware that somebody else, outside your
organization, has the SYSDBA privilege and can see all your data. That
sort of limits what you can put into the RDS.

Regards


On 7/27/2018 9:36 AM, Reen, Elizabeth (Redacted sender elizabeth.reen

We have a similar cloud here.  My question is how do I restore a table
in dev?  Or refresh a testing database from Prod?  It’s not the best
answer in the world.
Liz
Elizabeth Reen
CPB Database GroupManager
Service Now Group: CPB-ORACLE-DB-SUPPORT
*Sent:* Friday, July 27, 2018 6:54 AM
*To:* Oracle-l
*Subject:* Access/dump datafile (no alter system/database, e.g. AWS RDS)
Hi all,
I wonder if there is a way how to get to the datafiles in say Amazon
RDS, as there is no filesystem access. And the users cannot have alter
system or alter database grants, so I can't even dump contents of a
data block.
The backups are file system snapshots, but these don't seem to be accessible, either.
Or am I missing somethings, is there a way how to access an arbitrary datafile block?
Thanks for all ideas!
Vit

[Ruel, Chris]

Have you tried creating a directory to the file location and then using DBMS_FILE_TRANSFER? This is how you can move DataPump files into/out of RDS. Not sure if it would work with a datafile. They may have some constraints/intelligence in place to prevent this.

_____________________________________________________________________
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
***@lfg.com<mailto:***@lfg.com> * Desk:317.759.2172 * Cell 317.523.8482

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of vit.spinka
Sent: Friday, July 27, 2018 6:54 AM
To: Oracle-l <oracle-***@freelists.org>
Subject: Access/dump datafile (no alter system/database, e.g. AWS RDS)

***This email is from an external source. Only open links and attachments from a Trusted Sender.***
Hi all,

I wonder if there is a way how to get to the datafiles in say Amazon RDS, as there is no filesystem access. And the users cannot have alter system or alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.

Or am I missing somethings, is there a way how to access an arbitrary datafile block?

Thanks for all ideas!

Vit
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

["Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)]

Yes, this is how we do it now, but it is a real pain and assumes that we have the required extra space free.

Liz

Elizabeth Reen
CPB Database Group Manager
718.248.9930 (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT


From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Ruel, Chris
Sent: Friday, July 27, 2018 10:23 AM
To: ***@vitspinka.cz; Oracle-l
Subject: RE: Access/dump datafile (no alter system/database, e.g. AWS RDS)

Have you tried creating a directory to the file location and then using DBMS_FILE_TRANSFER? This is how you can move DataPump files into/out of RDS. Not sure if it would work with a datafile. They may have some constraints/intelligence in place to prevent this.

_____________________________________________________________________
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
***@lfg.com<mailto:***@lfg.com> * Desk:317.759.2172 * Cell 317.523.8482

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of vit.spinka
Sent: Friday, July 27, 2018 6:54 AM
To: Oracle-l <oracle-***@freelists.org>
Subject: Access/dump datafile (no alter system/database, e.g. AWS RDS)

***This email is from an external source. Only open links and attachments from a Trusted Sender.***
Hi all,

I wonder if there is a way how to get to the datafiles in say Amazon RDS, as there is no filesystem access. And the users cannot have alter system or alter database grants, so I can't even dump contents of a data block.
The backups are file system snapshots, but these don't seem to be accessible, either.

Or am I missing somethings, is there a way how to access an arbitrary datafile block?

Thanks for all ideas!

Vit

Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**