Oracle Export-Import - Refresh Table

-

Oracle Export-Import - Refresh Table

For DBA Refreshing Table, Schema and Database are routine activity. You can refresh/Clone database using below method:

1.Conventional Restore method and Rename DB post DB Refresh
2.Conventional Duplicate Database method
3. Active database Cloning 
4.Backup Base Cloning

In Todays blog , We will discuss about how to refresh table from one database to another database. 

Below are the Steps to refresh table:

1)Export the XXX.XXX_DATA table on Source Database

 expdp \"/ as sysdba\" directory=DATAPUMPDIR logfile=XXXX_XXX_adhoc_180113.log dumpfile=XXXX_XXXX_adhoc_180113.dmp tables=XXX.XXXX_DATA


2) SCP the backup dump to target server .

3)At Target Database ,Take the export backup of the existing table as below.

expdp \"/ as sysdba\" directory=DATAPUMPDIR logfile=XXXX_XXXX_adhoc_backup_180113.log dumpfile=XXXX_XXXX_adhoc_backup_180113.dmp tables=XXX.XXXX_DATA


4) Finally Import it as below

impdp \"/ as sysdba\" directory=DATAPUMPDIR logfile=XXXX_XXXX_adhoc_180113.log dumpfile=XXXX_XXXX_adhoc_180113.dmp tables=AIA.XXXX_XXXX TABLE_EXISTS_ACTION=TRUNCATE


5) Compare the indexes/constraints on both source and target

select owner,index_name,table_name from dba_indexes where table_name='XXXX_XXXX';

6) For any error related to Dependent object use below commands to disable the constraints first and run the import again.

select 'ALTER TABLE ' || owner || '.' || table_name || ' disable constraint ' ||constraint_name || ';' from dba_constraints where owner='&user' and TABLE_NAME='<XXXX>';

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for Refresh

Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

How to clone Pluggable Database from one container to different Container Database

-
Image
How to clone Pluggable Database from one container to different Container Database Cloning and Refresh are day-to-day activity which DBA perform on regular basic. In today blog , We will discuss about Cloning of the pluggable database from one container database to different container database. Oracle offers to unplug a PDB from one CDB and then plug into a different CDB. This will be another additional feature of PDB to give the highest availability and scalability the database systems may need in a cloud infrastructure environment.  1)We have two container database cseeddb(Source Container DB) and ctestdb1 (target container DB). NOTE: We have created container DB ctestdb1 and it does not have any PDB(Pluggable DB). 2)Verify you are in the correct container database root namespace(CDB). NOTE: We are on first container (cseeddb) database where we have clonedb pluggable database which we will move in to another empty container ctestdb1. SQL> set echo on SQL> SET LINESIZE 20...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more