Full database export using
expdp
Enable Minimal Supplemental Logging in Oracle on
source
alter database add supplemental log data ;
Enable Supplemental Logging at Table Level on
source
GGSCI> dblogin userid xxxxx password xxxxxx
GGSCI> add trandata <schema>.<tablename>
Add Extract, Add Exttrail, Add Pump, Add
Rmttrail on source
Start Extract, Start Pump on source
Create a database directory:
SQLPLUS> create directory dumpdir as
'<some directory>' ;
Get the current SCN on the source database:
SQLPLUS> select current_scn from v$database ;
Run
the Data Pump Export using the flashback SCN you obtained in the previous step.
The following example shows running the expdp utility at a Degree Of
Parallelism (DOP) of 4. If you have sufficient system resources (CPU, memory
and IO) then running at a higher DOP will decrease the amount of time it takes
to take the export (up to 4x for a DOP of 4). Note that expdp uses Oracle
Database parallel execution settings (e.g. parallel_max_servers) which have to
be set appropriately in order to take advantage of parallelism. Other processes
running in parallel may be competing for those resources. See the Oracle
Documentation for more details.
>expdp
directory=dumpdir full=y parallel=4 dumpfile=ora102_%u.dmp flashback_scn=28318029
>Username:
system NOTE: any DB user with DBA privileges will do >Password:
Note: The export log needs to be checked for errors.
Start
an import using impdp to the target database when the export step is complete.
Add
and Start Replicat after import is complete:
GGSCI>
add replicat <repname>, exttrail ./dirdat/<xx>
GGSCI>
start replicat <repname>, aftercsn <value returned from step 6>
Example 2:
schema export using exp
Enable
Minimal Supplemental Logging in Oracle on source
SQLPLUS
> alter database add supplemental log data;
Enable
Supplemental Logging at Table Level on source
GGSCI>
dblogin userid xxxxx password xxxxxx
GGSCI>
add trandata <schema>.<tablename>
Add Extract, Add
ExtTrail FileTrail File, Add Pump, Add RmtTrail FileTrail File on source
Start
Extract, Start Pump on source
Get the current SCN on the source database:
SQLPLUS>
select current_scn from v$database ;
CURRENT_SCN
-----------------------
28318029
Get
a flashback snapshot from the SCN that you obtained in the previous step. You
can do this with exp or expdp utility. The following example shows the use of
export utility (exp):
>exp
<username>/<password> owner=<schema_name> grants=n
statistics=none triggers=n compress=n FLASHBACK_SCN=28318029
Note:
Undo_retention has to be set high enough, and the export log needs to be
checked for errors. You can speed up exp by running multiple session in
parallel but you have to manually configure subsets of the data you want to
export (e.g. different tables for different export files). Make sure to use the
same FLASHBACK_SCN for all export sessions to ensure taking a read consistent
backup.
Start
an import to the target database when export is complete.
Add
and Start Replicat:
GGSCI>
add replicat <rename>, exttrail ./dirdat/<xx>
GGSCI> start
replicat <rename>, aftercsn <SCN value returned from query on
source>
