Initial Load - Instantiation - Oracle Golden Gate using Datapump

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 ; 


28318029



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>

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more