1

I need to do some structural changes to a database (alter tables, add new columns, change some rows etc) but I need to make sure that if something goes wrong i can rollback to initial state:

  • All needed changes are inside a SQL script file.
  • I don't have administrative access to database.
  • I really need to ensure the backup is done on server side since the BD has more than 30 GB of data.
  • I need to use sqlplus (under a ssh dedicated session over a vpn)
  • Its not possible to use "flashback database"! It's off and i can't stop the database.

Am i in really deep $#$%?

Any ideas how to backup the database using sqlplus and leaving the backup on db server?

Improve this question
1
  • If you don't have admin privs, you can't use RMAN. Commented Apr 18, 2014 at 17:55

3 Answers 3

Reset to default
5

better than exp/imp, you should use rman. it's built specifically for this purpose, it can do hot backup/restore and if you completely screw up, you're still OK.

One 'gotcha' is that you have to backup the $ORACLE_HOME directory too (in my experience) because you need that locally stored information to recover the control files.

a search of rman on google gives some VERY good information on the first page.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

rman requires system or sys login to create the catalog to to backups. But, given DBA access, rman is much better than imp/exp or datapump.
I assume that if somebody has to do a backup they have these permissions. Otherwise, they're so hamstrung from the beginning they can't possibly be effective.
2

An alternate approach might be to create a new schema that contains your modified structures and data and actually test with that. That presumes you have enough space on your DB server to hold all the test data. You really should have a pretty good idea your changes are going to work before dumping them on a production environment.

I wouldn't use sqlplus to do this. Take a look at export/import. The export utility will grab the definitions and data for your database (can be done in read consistent mode). The import utility will read this file and create the database structures from it. However, access to these utilities does require permissions to be granted, particularly if you need to backup the whole database, not just a schema.

That said, it's somewhat troubling that you're expected to perform the tasks of a DBA (alter tables, backup database, etc) without administrative rights. I think I would be at least asking for the help of a DBA to oversee your approach before you start, if not insisting that the DBA (or someone with appropriate privileges and knowledge) actually perform the modifications to the database and help recover if necessary.

Improve this answer

Comments

1

Trying to back up 30GB of data through sqlplus is insane, It will take several hours to do and require 3x to 5x as much disk space, and may not be possible to restore without more testing.

You need to use exp and imp. These are command line tools designed to backup and restore the database. They are command line tools, which if you have access to sqlplus via your ssh, you have access to imp/exp. You don't need administrator access to use them. They will dump the database (with al tables, triggers, views, procedures) for the user(s) you have access to.

Improve this answer

1 Comment

Seems to be a good approach near of what i'm looking for. Thks :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.