Blog All Title

This will be some fiun stuff. Before you install we need to determine the basic pre-installation requirements

• Hardware Requirements
  grep MemTotal /proc/meminfo # Gives the RAM Avaiable in linux
  
  get-wmiobject Win32_processor -computername # in windows using pwershell or you can use computer mananagemt and find it
  get-wmiobject Win32_ComputerSystem –> # Memory information
  
  

Excluding tables while importing
$ORACLE_HOME/bin/impdp \’/ as sysdba\’ remap_schema=suite_1_1_prod:suite_1_1_prod_stage DIRECTORY=DATADUMP_RAID dumpfile=$suite_Dumpname.dmp logfile=impdp_$suite_Dumpname.log remap_tablespace=suite:suite_stage
“EXCLUDE=TABLE:\"IN (’CATISSUE_AUDIT_EVENT_LOG’,'CATISSUE_AUDIT_EVENT_DETAILS’,
‘CATISSUE_AUDIT_EVENT_QUERY_LOG’, #’CATISSUE_AUDIT_EVENT’)\"”

Impdp issues
When importing data with datapump and remapping the schema and/or the tablespace, the final stage of the import takes quite a dramatic time to complete, even more than the actual data import.
The datapump import utility shows as last line in many situation:
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Solution

• Exclude statistics:
  impdp \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR DUMPfile= schema_CLINPP120090721_2110.dmp LOGFILE=impdp_schema_CLINPP120090707_2110.log JOB_NAME=import2clinpq2 REMAP_SCHEMA=CLINPP1:clinpq2 exclude=statistics
• GATHER STATISTICS :
  EXEC DBMS_STATS.gather_schema_stats (ownname => ‘CLINPQ2′,cascade =>true,estimate_percent => dbms_stats.auto_sample_size,options => ‘GATHER AUTO’,granularity=>’ALL’)

Excluding tables while importing
$ORACLE_HOME/bin/impdp \’/ as sysdba\’ remap_schema=suite_1_1_prod:suite_1_1_prod_stage DIRECTORY=DATADUMP_RAID dumpfile=$suite_Dumpname.dmp logfile=impdp_$suite_Dumpname.log remap_tablespace=suite:suite_stage
“EXCLUDE=TABLE:\"IN (’CATISSUE_AUDIT_EVENT_LOG’,'CATISSUE_AUDIT_EVENT_DETAILS’,
‘CATISSUE_AUDIT_EVENT_QUERY_LOG’, #’CATISSUE_AUDIT_EVENT’)\"”

1) Run the SQL query

SQL>SELECT * FROM DBA_DATAPUMP_JOBS

[oracle@biomedinfo ~]$ expdp ‘userid="sys/persistent as sysdba"‘ attach={JOB_NAME from above query}
Export> KILL_JOB

… [more]

1) Export the tablespace

expdp ‘userid=sys/password as sysdba’ DIRECTORY=imp_EXP DUMPfile=ts_REFDATA.dmp LOGFILE=ts_REFDATA.LOG TABLESPACES=REFDATA

2)Import and remap

expdp ‘userid=sys/password as sysdba’ DIRECTORY=imp_EXP DUMPfile=ts_REFDATA.dmp LOGFILE=ts_REFDATA.LOG TABLESPACES=REFDATA
REMAP_TABLESPACE=REFDATA:RESOLVEDDATA

if you have merge mutiple tablespaces into one then
1) Take an expdp of the tablesspaces

expdp ‘userid=sys/password as sysdba’ DIRECTORY=imp_EXP DUMPfile=ts_REFDATA.dmp LOGFILE=ts_REFDATA.LOG TABLESPACES=REFDATA, refdate1

2)Import and remap

expdp ‘userid=sys/password as sysdba’ DIRECTORY=imp_EXP DUMPfile=ts_REFDATA.dmp LOGFILE=ts_REFDATA.LOG TABLESPACES=REFDATA
REMAP_TABLESPACE=REFDATA:RESOLVEDDATA,refdate1:RESOLVEDDATA

export and import to shrink a tablespace
1) export the tablespace

expdp \’/ as sysdba\’ DIRECTORY=TEMP_DIR1 DUMPFILE=TEMP_DIRROMOTER_DATA.dmp LOGFILE=TEMP_DIR:exp_PROMOTER_DATA.log tablespaces=PROMOTER_DATA

2) Import the tablespace

impdp \’/ as sysdba\’ DIRECTORY=TEMP_DIR1 DUMPFILE=TEMP_DIRROMOTER_DATA.dmp LOGFILE=TEMP_DIR:impdp_PROMOTER_DATA.log

Export and import to a schema into mutiple dump files using FILEsize option parameter file

• Export the schema
• Expdp cmd : expdp \’/ as sysdba\’ parfile=/home/oracle/chip_admin.par
• contents of chip_admin.par
  

  DIRECTORY=DATA_PUMP_DIR
  SCHEMAS=CHIP_ADMIN
  DUMPFILE=DATA_PUMP_DIR:CHIP_ADMIN_%U
  FILESIZE=32G
  LOGFILE=DATA_PUMP_DIR:CHIP_ADMIN.log
  PARALLEL=2

• scp the dump files to the new location
• Expdp cmd : impdp \’/ as sysdba\’ parfile=/home/oracle/impdpchip_admin.par

• contents of impdpchip_admin.par

  DIRECTORY=TEMP_DIR1
  DUMPFILE=TEMP_DIR:CHIP_ADMIN_%U.dmp
  LOGFILE=TEMP_DIR:imp_chip_admin.log
  JOB_NAME=import2CHIP_ADMINMUtipelinputs
  REMAP_SCHEMA=CHIP_ADMIN:CHIP_ADMIN
  REMAP_TABLESPACE=REFDATA:MGACHIP_ADMIN,CHIPDATA:MGACHIP_ADMIN,
  TEXTINDX:MGACHIP_ADMIN,USERS:MGACHIP_ADMIN,PUBMEDDATA:MGACHIP_ADMIN,
  INDX:MGACHIP_ADMIN,SEQDATA:MGACHIP_ADMIN,LOBDATA:MGACHIP_ADMIN,
  POSTDATA:MGACHIP_ADMIN,PUBMED_DATA:MGACHIP_ADMIN,
  PUBMED_INDEX:MGACHIP_ADMIN,CACOREDATA:MGACHIP_ADMIN