Oracle goldengate
初始化数据的方法
Oracle goldengate instantiation
方法:
一、
coning with oracle rman
二、
oracle EXPDP/IMPDP or EXP/IMP Utilities
一、coning with oracle rman
On the Source (RMAN refers to as target):
Add and Start Rxtract:
Enable
Minimal Supplemental Logging in Oracle
SQLPLUS
>
alter
database add supplemental log data
Enable
Supplemental Logging at Table Level
GGSCI>
dblogin userid
xxxxx password xxxxxx
GGSCI>
add trandata
.
Add Extract, Add Exttrail,
Add Pump, Add Rmttrail
Start Extract, Start Pump
Recover database on target
database:
Create
init.ora file for the destination .
Run
the following SQL (if you currently use an spfile; if you already use a pfile
then you can use the pfile and skip this step)
SQLPLUS>
create pfile=
‘
/init.ora
’
from spfile ;
Copy
init.ora file to target (to $ORACLE_HOME/dbs directory on
destination)
sftp>
put
init.ora
Backup
source database
OS>
RMAN target /
RMAN>
backup database
plus archivelog ;
Copy
backup files to destination located in the same location as they were written
on the source (default is $ORACLE_HOME/dbs but it may have been changed using
the CONFIGURE command in RMAN).
sftp>
Once
backup completes, capture SCN to clear datafile fuzziness
Instantiation
from an Oracle Source Database: Oracle GoldenGate
RMAN>
restore database
preview summary;
Note
the SCN returned, it will appear at the end of the command like:
Media recovery start SCN is
34822578
Recovery must be done beyond SCN
34822578 to clear datafile fuzziness
Finished restore at 10-OCT-10
On the Destination (RMAN refers to as
auxiliary) :
Add
a tnsnames.ora entry (in $ORACLE_HOME/network/admin) for the source database.
Create
the password file (in the $ORACLE_HOME/dbs directory):
$ORACLE_HOME/dbs>
orapwd
file=orapwpassword=
Start
up auxiliary instance (make sure the $ORACLE_SID is set correctly and the
init.ora is in $ORACLE_HOME/dbs)
SQLPLUS>
startup nomount
Create
duplicate database. This step will open the database
OS>
RMAN target
sys/@
RMAN>
connect auxiliary
/
RMAN>
duplicate target
database to nofilenamecheck until scn ;
Note:
you can also run the duplicate database command by running RMAN on the source.
In that case you use connect target / on the source, and connect auxiliary
sys@.
In
either case you must copy the backup files to the destination server. Also for
this scenario you must include a tnsnames entry for the target database which
would be added to the tnsnames.ora file on the host with the auxiliary. In
addition, on the source server, you must have listener configured to accept
requests for the destination database in a nomount state. This is done by
explicitly including the service/SID in the listener.ora configuration file.
Once
complete review the Oracle alert log to get last SCN that the database was
recovered to. Look in the oracle alert log for the following entry:
RESETLOGS after incomplete
recovery UNTIL CHANGE
34822578
After 34822578 in this sample is the
SCN where we want Replicat to start processing
Add
Temp file (before Oracle 10.2; starting with Oracle 10.2 Oracle will
automatically add temp files and you can skip this step)
SQLPLUS>
alter tablespace
temp add tempfile
‘
/
‘
size
Create
spfile (if you want to use an spfile; if not you can skip this step and the
next step below):
SQLPLUS>
create spfile
from pfile=
‘
$ORACLE_HOME/dbs/init.ora
’
;
Stop
and Restart Database to pick up spfile:
SQLPLUS>
shutdown
immediate
SQLPLUS>
startup
Add and Start Replicat:
GGSCI>
add replicat
, exttrail ./dirdat/
GGSCI>
start replicat
, aftercsn <
value
returned from alert log for SCN database was recovered to>
二、
oracle EXPDP/IMPDP or EXP/IMP Utilities
Example 1: full database export using
expdp
Add and Start Rxtract:
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
.
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
‘
‘
;
Get the current SCN on the source
database:
SQLPLUS>
select
current_scn from v$database ;
CURRENT_SCN
-----------------------
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
, exttrail ./dirdat/
GGSCI>
start replicat
, aftercsn
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
.
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
-----------------------
3410138433
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
/wner=grants=n statistics=none
triggers=n compress=n FLASHBACK_SCN=3410138433
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
, exttrail ./dirdat/
GGSCI>
start replicat
, aftercsn
总结:
转MOS:
ID 1276058.1 ;
以备日后参考