Feeds:
Posts
Comments

Posts Tagged ‘export’

Dear Friends,

          Back to work after long time, was busy in trekking. Today we did database migration from 9i to 10g using Export / Import.

To upgrade a database using the Export/Import utilities kindly follow below mention steps.

  •  Keep database in restricted mode – Export data from the current database
    exp  FILE=exp_20092011.dmp FULL=y GRANTS=y BUFFER=4096  ROWS=y CONSISTENT=y

 

  • Now once export is done prerequisite for import :

1) Please verify for sysaux tablespace is available. And also the 10g parameter properly set.

2)Please create the required Users and Tablespace as per the requirement.

Query yo check  User with respective privilages.

select

  lpad(‘ ‘, 2*level) || granted_role “User, his roles and privileges”

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper(‘%&enter_username%’)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

 
 Query to check Tablespaces and there sizes.

set line 120

col host_name for a20;

select w.name,Q.host_name,a.tablespace_name,round(b.total_mb/1024/1024,2) total_mb,

       round(x.maxbytes_mb/1024/1024,2) maxbytes_mb,

       round(nvl(c.used_mb/1024/1024,0),2) Used_Mb,

       round(nvl(d.free_mb/1024/1024,0),2) Free_mb,

       round(((nvl(Used_mb,1)/decode(maxbytes_mb,NULL,total_mb,0,total_mb,maxbytes_mb))*100),2) Used_percent

from   dba_tablespaces a, (select tablespace_name,bytes Total_Mb from sys.sm$ts_avail) b,

       (select tablespace_name,bytes Used_mb from sys.sm$ts_used) c,

       (select tablespace_name,bytes free_mb from sys.sm$ts_free) d, v$database w, v$instance Q,

       (select tablespace_name,sum(maxbytes) maxbytes_mb from dba_data_files group by tablespace_name) x

where  a.tablespace_name=b.tablespace_name(+)

and    a.tablespace_name=c.tablespace_name(+)

and    a.tablespace_name=x.tablespace_name(+)

and    a.tablespace_name=d.tablespace_name(+) order by 8 desc;

 

You can get the DDL of tablespace using below script.

set pagesize 0

set escape on

spool ‘\tablespace.sql’

select ‘create tablespace ‘ || df.tablespace_name || chr(10)

|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes

 || decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘

 || maxbytes)

 || chr(10)

 || ‘default storage ( initial ‘ || initial_extent

 || decode (next_extent, null, null, ‘ next ‘ || next_extent )

|| ‘ minextents ‘ || min_extents

|| ‘ maxextents ‘ ||  decode(max_extents,’2147483645′,’unlimited’,max_extents)

 || ‘) ;’

from dba_data_files df, dba_tablespaces t

where df.tablespace_name=t.tablespace_name;

set pagesize 100

set escape off

spool off

 

You can get the DDL of  Users using below script.

set pagesize 0

set escape on

spool users.sql

select ‘create user ‘ || U.username || ‘ identified ‘ ||

DECODE(password,

      NULL, ‘EXTERNALLY’,

      ‘ by values ‘ || ”” || password || ””

      )

|| chr(10) ||

‘default tablespace ‘ || default_tablespace || chr(10) ||

‘temporary tablespace ‘ || temporary_Tablespace || chr(10) ||

‘ profile ‘ || profile || chr(10) ||

‘quota ‘ ||

decode ( Q.max_bytes, -1, ‘UNLIMITED’, NULL, ‘UNLIMITED’, Q.max_bytes) ||

‘ on ‘ || default_tablespace ||

decode (account_status,’LOCKED’, ‘ account lock’,

                                                ‘EXPIRED’, ‘ password expire’,

                                                ‘EXPIRED \& LOCKED’, ‘ account lock password expire’,

                                                null)

||

‘;’

from dba_users U, dba_ts_quotas Q

— Comment this clause out to include system & default users

where U.username not in (‘SYS’,’SYSTEM’,

‘SCOTT’,’DBSNMP’,’OUTLN’,’WKPROXY’,’WMSYS’,’ORDSYS’,’ORDPLUGINS’,’MDSYS’,

‘CTXSYS’,’XDB’,’ANONYMOUS’,’OWNER’,’WKSYS’,’ODM_MTR’,’ODM’,’OLAPSYS’,

‘HR’,’OE’,’PM’,’SH’,’QS_ADM’,’QS’,’QS_WS’,’QS_ES’,’QS_OS’,’QS_CBADM’,

‘QS_CB’,’QS_CS’,’PERFSTAT’)

and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)

;

set pagesize 100

set escape off

spool off

 

Now run this  tablespace.sql  and users.sql  to create tablespace and users  in 10g.
[Note : Actually we need not create  user and tablespace while importing DB in 10g]
3)Make sure that 10g DB is in Non-Archive log mode.

 imp FILE=exp_20092011.dmp LOG=imp_20092011.log FULL=Y GRANTS=Y BUFFER=4096 ROWS=Y

  • Once import is done please follow the below steps:

 1)Once import is done please Check import logs for errors.
 
2)Make sure users are pointing to proper tablespaces.

 select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
 
3)Please check the total object count and respective schema object count and confirm with original.

 select OBJECT_TYPE,count(*)from dba_objects where owner=’CROSSLNK’ group by OBJECT_TYPE;

 4)Please check the roles and privilages for respective user and do the required changes for same if required..

 select

  lpad(‘ ‘, 2*level) || granted_role “User, his roles and privileges”

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper(‘%&enter_username%’)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

 
 
5)Please check the invalid object count.Recompile for invalid objects (run utrp.sql)

   select count(*) from dba_objects where STATUS=’INVALID’;
 
6)Gather statistics for entire database
 
 DATABASE LEVEL

begin

 dbms_stats.gather_database_stats(

options=> ‘GATHER AUTO’);

 end;

7) Put the 10g DB back into ARCHIVELOG mode if required.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  272629760 bytes

Fixed Size                   788472 bytes

Variable Size             103806984 bytes

Database Buffers          167772160 bytes

Redo Buffers                 262144 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

8) Verify the backup is configured properly.

9)Monitor the alert log and dump directories for possible issues.

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”

Advertisements

Read Full Post »

Export and Import Dump

Export and Import Dump

————Importing Full Dump File————-

If you want to Import all the objects in a dump file then you can type the

following command.

$impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y

LOGFILE=dpump_dir2:full_imp.log

This example imports everything from the expfull.dmp dump file. In this example,

a DIRECTORY parameter is not provided. Therefore, a directory object must be provided

on both the DUMPFILE parameter and the LOGFILE parameter

————-Importing Objects of One Schema to another Schema—————

The following example loads all tables belonging to hr schema to scott schema

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_SCHEMA=hr:scott

If SCOTT account exist in the database then hr objects will be loaded into scott schema.

If scott account does not exist, then Import Utility will create the SCOTT account with

an unusable password because, the dump file was exported by the user SYSTEM and imported

by the user SYSTEM who has DBA privileges.

————-Loading Objects of one Tablespace to another Tablespace——————–

You can use remap_tablespace option to import objects of one tablespace to another

tablespace by giving the command

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_TABLESPACE=users:sales

The above example loads tables, stored in users tablespace, in the sales tablespace.

———Generating SQL File containing DDL commands using Data Pump Import———

You can generate SQL file which contains all the DDL commands which Import would have

executed if you actually run Import utility

The following is an example of using the SQLFILE parameter.

$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

SQLFILE=dpump_dir2:expfull.sql

A SQL file named expfull.sql is written to dpump_dir2.

———————–Importing objects of only a Particular Schema———————

$impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log

DUMPFILE=expdat.dmp

—————–Importing Only Particular Tables—————

The following example shows a simple use of the TABLES parameter to import only the employees and jobs tables from the expfull.dmp file. You can create the expfull.dmp dump file used in this example by running the example provided for the Full Database Export in Previous Topic.

$impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Before Importing Create a user

SQL> create user jretail1 identified by jretail*****;

User created.

Grant privileges to the user

SQL> grant resource,connect,create view to jretail1;

Grant succeeded.

Then start the import process

SQL> $imp jretail1@test file=c:jretail1120am.dmp log=c:jretail1120am.log fromuser=jretail touser=jretail1;

Import: Release 10.1.0.2.0 – Production on Thu Oct 22 14:58:32 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by JRETAIL, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table “ACCOUNTMASTER” 3203 rows imported
. . importing table “ACCOUNTOPENING” 4345 rows imported
. . importing table “AGEINGMASTER” 6 rows imported
. . importing table “APPLICATIONCONFIGURATION” 2 rows imported
. . importing table “BILLWISEADJUSTMENT” 462 rows imported
. . importing table “BILLWISEADJUSTMENTSCHEDULE” 462 rows imported
. . importing table “BUDGETMASTERDETAIL” 4 rows imported
. . importing table “BUDGETMASTERHEADER” 1 rows imported
. . importing table “CALENDARMASTER” 4 rows imported
. . importing table “CALENDARMONTHDETAIL” 0 rows imported
. . importing table “CHITMASTER” 10 rows imported
. . importing table “CHITMEMBER” 850 rows imported
. . importing table “CHITMEMBERINSTALLMENTDETAIL” 7107 rows imported
. . importing table “CHITRECEIPTDETAIL” 6351 rows imported
. . importing table “ITEMTYPEATTRIBUTESDETAIL” 2 rows imported
About to enable constraints…
Import terminated successfully without warnings.

SQL>

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Before Importing Create a user

SQL> create user visynapse identified by visynapse*******;

User created.

Grant privileges to the user

SQL> grant resource,connect,create view to visynapse;

Grant succeeded.

Then start the import process

SQL> $imp visynapse@test file=E:/uploaddb_19_10_09.dmp log=E:/implogvisyn.log;

Import: Release 10.1.0.2.0 – Production on Mon Oct 19 12:50:41 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Password:*****

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing VISYNAPSE’s objects into VISYNAPSE
. . importing table “CALCULATION_SECURITY” 199 rows imported
. . importing table “CALCULATION_TABLE” 207 rows imported
. . importing table “COLOUR” 1 rows imported
. . importing table “CUBE” 8 rows imported
. . importing table “DASHBOARD” 2 rows imported
. . importing table “DASHBOARD_METRIC” 8 rows imported
. . importing table “DASH_TABLE” 3 rows imported
. . importing table “DATA_DICTIONARY” 48 rows imported
. . importing table “DIMENSION_26” 295 rows imported
. . importing table “DIMENSION_27” 432 rows imported
. . importing table “DIMENSION_28” 295 rows imported
. . importing table “DIMENSION_SECURITY” 19 rows imported
. . importing table “DOMAIN” 1 rows imported
. . importing table “FILTERS” 0 rows imported
. . importing table “GRAINS” 6148 rows imported
. . importing table “HEADER_FOOTER” 0 rows imported
. . importing table “KEY_PERFORMANCE_INDICATOR” 0 rows imported
. . importing table “KEY_PERFORMANCE_INDICATOR_CS” 0 rows imported
. . importing table “KPI_DIMENSION” 0 rows imported
. . importing table “KPI_DIMENSION_CS” 0 rows imported
. . importing table “LINKMETRIC” 0 rows imported
. . importing table “METRIC” 8 rows imported
. . importing table “METRIC_CALCULATION_SET” 207 rows imported
. . importing table “METRIC_CUBE_SET” 8 rows imported
. . importing table “REPORT_CHANNEL” 0 rows imported
. . importing table “REPORT_CONFIG” 23 rows imported
. . importing table “REPORT_HEADER” 0 rows imported
. . importing table “SCORECARD” 0 rows imported
. . importing table “SELECTION_DIMENSION” 0 rows imported
. . importing table “SELECTION_FILTERCRITERIA” 0 rows imported
. . importing table “SELECTION_FORMULA” 0 rows imported
. . importing table “SELECTION_SHAREDREPORT” 0 rows imported
. . importing table “SELECTION_TABLE” 0 rows imported
. . importing table “STATIC_REPORT_TABLE” 8 rows imported
. . importing table “SUMMARY_CONFIG” 0 rows imported
. . importing table “TIME_FREQUENCY” 6 rows imported
. . importing table “USER_DASHBOARD_SECURITY” 2 rows imported
. . importing table “USER_GROUP” 1 rows imported
. . importing table “USER_PROFILES” 1 rows imported
. . importing table “USER_TARGETS” 8 rows imported
. . importing table “VALUE_SECURITY” 0 rows imported
About to enable constraints…
Import terminated successfully without warnings.

SQL>

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

SQL> $exp visynapse@uploaddb file=E:/uploaddb_19_10_09.dmp log=E:/uploadlog.log;

Export: Release 10.1.0.2.0 – Production on Mon Oct 19 11:18:48 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Password:******

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user VISYNAPSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user VISYNAPSE
About to export VISYNAPSE’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VISYNAPSE’s tables via Conventional Path …
. . exporting table CALCULATION_SECURITY 199 rows exported
. . exporting table CALCULATION_TABLE 207 rows exported
. . exporting table COLOUR 1 rows exported
. . exporting table CUBE 8 rows exported
. . exporting table DASHBOARD 2 rows exported
. . exporting table DASHBOARD_METRIC 8 rows exported
. . exporting table DASH_TABLE 3 rows exported
. . exporting table DATA_DICTIONARY 48 rows exported
. . exporting table DEFINITIONS_21 21 rows exported
. . exporting table DEFINITIONS_22 12 rows exported
. . exporting table DEFINITIONS_23 17 rows exported
. . exporting table DEFINITIONS_24 30 rows exported
. . exporting table DEFINITIONS_25 22 rows exported
. . exporting table DEFINITIONS_26 25 rows exported
. . exporting table DEFINITIONS_27 23 rows exported
. . exporting table DEFINITIONS_28 28 rows exported
. . exporting table DIMENSION_SECURITY 19 rows exported
. . exporting table DOMAIN 1 rows exported
. . exporting table FILTERS 0 rows exported
. . exporting table GRAINS 6148 rows exported
. . exporting table HEADER_FOOTER 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR_CS 0 rows exported
. . exporting table KPI_DIMENSION 0 rows exported
. . exporting table KPI_DIMENSION_CS 0 rows exported
. . exporting table LINKMETRIC 0 rows exported
. . exporting table METRIC 8 rows exported
. . exporting table METRIC_CALCULATION_SET 207 rows exported
. . exporting table METRIC_CUBE_SET 8 rows exported
. . exporting table REPORT_CHANNEL 0 rows exported
. . exporting table REPORT_CONFIG 23 rows exported
. . exporting table REPORT_HEADER 0 rows exported
. . exporting table SCORECARD 0 rows exported
. . exporting table SELECTION_DIMENSION 0 rows exported
. . exporting table SELECTION_FILTERCRITERIA 0 rows exported
. . exporting table SELECTION_FORMULA 0 rows exported
. . exporting table SELECTION_SHAREDREPORT 0 rows exported
. . exporting table SELECTION_TABLE 0 rows exported
. . exporting table STATIC_REPORT_TABLE 8 rows exported
. . exporting table SUMMARY_CONFIG 0 rows exported
. . exporting table TIME_FREQUENCY 6 rows exported
. . exporting table USER_DASHBOARD_SECURITY 2 rows exported
. . exporting table USER_GROUP 1 rows exported
. . exporting table USER_PROFILES 1 rows exported
. . exporting table USER_TARGETS 8 rows exported
. . exporting table VALUES_28_CONTROL 196 rows exported
. . exporting table VALUE_SECURITY 0 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.

SQL>

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »