Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: September 06, 2016 - 10:24 am UTC

Last updated: September 09, 2016 - 7:58 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi team,


In our production database we are using logical backup and there is 20 users and the size of data 200 GB each user So it is configure mid night but export started after some time my entire database getting very slow it is find when we done export in a working hours and when find db hang or very slow we kill the export jobs then db works normally.

So, Pls suggest how to over come from this situation any idea to done export and db not affect or
we need to use any other technique.

Thanks

and Connor said...

Dont use logical backup as a (lone) backup method....that's a bad idea in my opinion. Look at using RMAN to backup your database, and then you can use block change tracking which means you only have to backup changes, not the entire database.

This might shrink your backup times dramatically.

If there are reasons you *cant* do this, please let us know and we'll explore other options.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, September 07, 2016 - 8:34 am UTC

Thanks for reply,

I have already suggested to do rman backup but they use the dump into another periodically so they want expdp because with rman they want only specific user import into another database so it is not possible So, They want expdp only.

1. If i will take rman backup then i can import schema only?
2. If they want export only then what is the other solution?

Pls suggest for the same.

Thanks
Connor McDonald
September 07, 2016 - 12:49 pm UTC

If this is a regular activity, I would take a one-off maintenance hit to move each user into separate tablespaces.

Then you can use transportable tablespaces. Still nice and efficient.

oracle oracle, September 07, 2016 - 1:23 pm UTC

Thanks

I would take a one-off maintenance hit to move each user into separate tablespaces.

Then you can use transportable tablespaces. Still nice and efficient.

Can you explain in details with example.

Thanks
Connor McDonald
September 08, 2016 - 5:45 am UTC

If all of the objects in user1 are in tablespace1, and user1 is self-contained (ie, does not have dependencies on any other users in the database), then you can transport that tablespace from one database to another.

This can be done with Enterprise Manager or with your own scripts, the basic process being:

- backup that particular tablespace
- copy the database

Some introductory info here

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11394

The basics are:

- make the tablespace readonly on source
- datapump extract the tablespace metadata
- make the tablespace readwrite on source
- copy the files and the dumpfile to the new database
- datapump import the tablespace metadata to attach the tablespace to a new database

If you need to do it without making the tablespace readonly, you can use RMAN to do it (which is a little more complicated);

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmttbsb.htm

But in a nutshell:

- RMAN starts an auxiliary instance.
- RMAN restores a backup of the source database control file to serve as the auxiliary instance control file and mounts this control file.
- RMAN restores auxiliary set and transportable set data files from the backups of the source database.
- RMAN stores the auxiliary data files in the selected auxiliary destination. The auxiliary destination is a disk location where RMAN can store auxiliary set files such as the parameter file, data files (other than those in the transportable set), control files, and online logs of the auxiliary instance during the transport. If the transport succeeds, then RMAN deletes these files.
- RMAN stores the transportable set files in the tablespace destination. The tablespace destination is a disk location that by default contains the data file copies and other output files when the tablespace transport command completes.
- RMAN performs database point-in-time recovery (DBPITR) at the auxiliary instance.
- RMAN opens the auxiliary database with the RESETLOGS options.
- RMAN places the transportable set tablespaces of the auxiliary instance into read-only mode. RMAN also invokes Data Pump Export in transportable tablespace mode to create the export dump file for the transportable set.
- RMAN also generates the sample Data Pump import script for use when plugging in the transported tablespaces at a target database. The contents of this script are written to a file named impscript.sql in the tablespace destination. The commands for the script are also included in the RMAN command output.
- If the preceding steps are successful, then RMAN shuts down the auxiliary instance and deletes all files created during the TRANSPORT TABLESPACE operation except for the transportable set files, the Data Pump Export file, and the sample import script.


A reader, September 09, 2016 - 2:10 am UTC

As you suggested I have convinced management to take rman backup of the database.

Now I plan to take weekly rman full backup with archive log all and everyday incremental level 1 backup with archive log.
1.At the time of Restoration how can I do ? Which backup I need to move on Restoration server if I want ot Restoration check.

2. Some database is in no archive log mode so I can do the same full database backup without archive backup Daily increment level 1 and weekly full and it Is without archive log so
It is possible to restore if yes then how?

Pls let me know so I can start this activity.

Thanks


Connor McDonald
September 09, 2016 - 7:58 am UTC

Check my previous reply - it doesnt work that way. Using the RMAN to transport tablespaces (without read only) is series of steps (as I summarised).

But please...consult the docs, do some prototyping and testing.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.