Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 14 May 2012

Restore or Recovery From Export Backup

  1. Create a new version of the affected database.
  2. Add new rollback segments.
  3. Recreate the required tablespaces in the database. Use the Create Tablespace script that you have prepared.
  4. Recreate all users. Use the script to Recreate all users. Modify this script after each and every create or change of users:
  5. Get a list of the full (or complete), cumulative and incremental exports online. Use the most current full or complete and subsequent cumulative and/or incremental exports. The commands are executed after setting the default directory to the exports storage location. In this example: /oracle1/ORCL3/admin/exports using the command:
    $cd /oracle1/ORCL3/admin/exports
  6. Import full, cumulative and incremental exports using the INCTYPE=RESTORE option.
    For Example:

Exports - Recovery of a Table

  1. Determine the last date that data was changed in the table(s). Select the first export after the last change date of the table. This export will be used to recover the table. If you are recovering multiple tables, several export files may be required.
  2. Use the following command to import the table(s) back into the database:
    $ IMP USERID=user/password TABLES=(table1, table2...table3)FILE=filename

Exports - Recovery of a User's Table

Using the last full or complete export, issue the following command:
    $ IMP USERID=user/password FROMUSER=username TOUSER=username file=filename
In this situation, the FROMUSER and TOUSER would be the same user.

Exports - Moving Tables from One User to Another

If you wish to move one user's tables and structures into another user's schema, issue the following command on an export of the user's files to be moved:
$ imp userid=user/password fromuser=username1 touser=username2 file=filename
Note: Where username1 is the current owner's name and username2 is the new owner's name.

Command Used to Generate a List of User's Objects

$ imp user/password indexfile=output_file file=filename
The output_file will contain the full text for all of the indexes, tables, and clusters for the export file. The table and cluster commands will be commented out, but with a bit of editing can be made useable. This parameter is wonderful, because it doesn't actually import anything, but uses the dump file to create an editable file that contains all 'CREATE TABLE' and 'CREATE INDEX' statements for the objects contained in the dump file. We recommend creating one of these files and then playing with the storage parameters and tablespace stuff.
When moving around information 'by schema', you can use the 'OWNER=' parameter and make export files for each of the specific schemas, then utilize the 'indexfile=' parameter on each import.

Export - Improving Run Time on Windows

If exports on Windows seem to be taking an unusually long time, check the setpoint for the NLS_LANG parameter in the registry editor for "software/oracle". The value should be set to US7ASCII (or the value that matches your NLS setpoint; this is the default). Having the correct value set can make a dramatic difference. To check this value, issue a query on the NLS_DATABASE_PARAMETERS view:
SELECT parameter,value FROM nls_database_parameters;

Importing/Exporting Partitions

You can use the Oracle Import/Export utility to export data from a specific partition rather than the entire partitioned table. You can export a partitioned table in table, user, and full modes. There are restrictions, though, on exporting individual partitions, and these can be exported in table mode only. To export a partition, use the tables = syntax and refer to the partition as table_name:partition.

Script1 for Capturing system Privileges granted to users and roles: script for capturing system privilegesScript2 : Script: To Generate Script for Create Tablespace


Post a Comment