Search

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, 6 August 2012

Restore or Refresh Schema using Import/Export with Toad

Database: Oracle 9i R2
OS: Windows 2003
Tools technique: Import/Export with TOAD
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scope and Application: We have ORAFIN schema in production database to maintain the oracle financial application. Now I have created a test database on different system and will restore the ORAFIN schema with the name ITGFIN. The purpose is to provide a test environment of oracle financial application to new user.
Overview: Export/Import is most common technique for backup and restoration in oracle 9i. Export is best media to move data between different operating system and it is much easier to replace a table that was dropped by accident with an import than by performing an incomplete recovery. This tool is improved in every version of oracle. For more information about export/import follow the link: Export/Import Utilities in Oracle 9i
As we know export/import can only be used against open database. Because export copies one table at a time, it can only guarantee read consistency for individual tables. Read consistency between tables is not guaranteed. For a read consistent export of the entire database, changes to the database must stop during the export. This can be enforced by closing the database and then reopening it in RESTRICT mode. For restore full database or to restore single table follow the link: Restore from Export backup
Scenario: Here we will export the original schema ORAFIN and import as schema ITGFIN for Test purpose. You can also used this method to refresh the schema (No need to change the schema name after creation of script just drop the previous schema and re-create using this script and restore the schema. For more information about refreshing schema using Import/export follow the link: Schema Refreshing in Oracle 9i

Step1: Export original schema ORAFIN. The script and log file attached below.
EXP SYSTEM/SYSMAN@sadhan.world OWNER=ORAFIN FILE=D:\backup\ orafin_dump.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= D:\backup\Orafin.DMP.LOG

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in AR8MSWIN1256 character set and UTF8 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ORAFIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ORAFIN
About to export ORAFIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ORAFIN's tables via Conventional Path ...
. . exporting table                        ACCFILE        963 rows exported
. . exporting table                    ACCFILE_BAK        345 rows exported
. . exporting table          ASSETS_ALL_DEASSEMBLE        508 rows exported
. . exporting table   ASSETS_ALL_DEASSEMBLE_HEADER         38 rows exported
. . exporting table             ASSETS_ALL_HISTORY       1766 rows exported
. .
. .
. .
. . exporting table                  USR_PRIVILEGE          0 rows exported
. . exporting table            VENDOR_JDA_BALANCES          0 rows exported
. . exporting table                        VSERIAL          0 rows exported
. . exporting table                  YEARLY_SPACES        311 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Step2: Create ORAFIN schema script using toad as show in below screen shot.





























Step3: Change the schema name ITGFIN in above script and apply this script to create the schema with all of its rights one by one.
CREATE USER ITGFIN
  IDENTIFIED ITGFIN
  DEFAULT TABLESPACE SDH_FIN_DBF
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for ITGFIN
  GRANT DBA TO ITGFIN;
  GRANT PLUSTRACE TO ITGFIN;
  ALTER USER ITGFIN DEFAULT ROLE ALL;
  -- 1 System Privilege for ITGFIN
  GRANT UNLIMITED TABLESPACE TO ITGFIN;
  -- 1 Tablespace Quota for ITGFIN
  ALTER USER ITGFIN QUOTA UNLIMITED ON SDH_FIN_DBF;
  -- 1 Object Privilege for ITGFIN
    GRANT DELETE, INSERT, SELECT, UPDATE ON SYSTEM.PLAN_TABLE TO ITGFIN;

Step4: Now Copy the Export dump into your system drive. I have copied on the location D:\BACKUP\ then restore the objects from this export dump with the below import command.
IMP SYSTEM/******@sadhan.world FILE=D:\BACKUP\orafin_dump.DMP FROMUSER=ORAFIN TOUSER=ITGFIN LOG=D:\BACKUP\orafin.log
Note: Before run the script you must check FROMUSER, TOUSER and export dump parameter.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AR8MSWIN1256 character set (possible charset conversion)
export client uses AR8MSWIN1256 character set (possible charset conversion)
. importing ORAFIN's objects into ITGFIN
. . importing table                      "ACCFILE"        963 rows imported
. . importing table                  "ACCFILE_BAK"        345 rows imported
. . importing table                  "APPLICATION"          1 rows imported
. . importing table                      "ASSETS1"        229 rows imported
. . importing table                   "ASSETS_ALL"      27025 rows imported
. .
. .
. .
. . importing table                "USR_PRIVILEGE"          0 rows imported
. . importing table          "VENDOR_JDA_BALANCES"          0 rows imported
. . importing table                      "VSERIAL"          0 rows imported
. . importing table                "YEARLY_SPACES"        311 rows imported
About to enable constraints...
Import terminated successfully without warnings.


Step5: Open the ITGFIN newly restored schema in toad and must put ‘Test environment’ from the Application and Company_profile table before using the oracle financial software. This is just a comment to display the Test environment to differentiate with the original environment.
Note: This is not the part of restore or refresh schema for DBA Please ignore the step5.

3 comments:

  1. Hello Sir,
    Very nice article to explain schema export import.
    I exported hr schema and import it again with new schema.

    i have some Q
    1)Is there any other way to know complete definition about a user like you did with TOAD.
    2)EXPORT parameter ROWS=Y what does it really mean?
    as if we are exporting table or schema rows will automatically exported.
    Other parameter GRANTS=Y what is this parameter used for.

    And Thanks for these kind of nice articles.

    ReplyDelete
    Replies
    1. Yes Navdeep, You can generate schema details, role and privilege through the script ‎check my earlier post related to import/export: Restore from Export dump
      Script: Capturing privilege, role and all of Grants, Script: To Generate Create Table
      If Grants is set to YES then grants will be exported with database objects. The default ‎value is YES (So System privilege grants are always exported) and ROW= Y determines ‎if rows of data is exported or just the object definitions. The default value is Y. So in fact ‎no use here it is just my automated scripts copy so whenever required we can change this ‎parameter ‘N’ and run this script‎

      Delete
  2. BlueHost is ultimately one of the best hosting provider with plans for any hosting requirements.

    ReplyDelete