oracle mos怎么用,Oracle 高可用 goldengate 初始化数据的方法(转MOS:ID 1276058.1)

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 ;

以备日后参考