"OGG" Goldengate for Oracle implementation document-Hyper-Detailed summary version ____oracle

Oracle to Oracle version

Usually to the customer production environment deployment reference documents, summary of the more detailed, mainly detailed description of the process of data initialization, parameters of different environments, so just write a relatively simple, according to the actual conditions to add it.

1. Pre-preparation 1.1. System Level

Windows needs to install c\c++ 2005 Runtime 1.2. Database level

Run Full-db_checkoracle.sql View the results and check for unsupported objects 2. Source-side Database configuration 2.1. Open Archive Mode

Check to see if the source-side database is in archive mode

Sql>archive log list;

For non-archive mode, change it to archive mode in the database Mount state

sql> ALTER DATABASE Archivelog;

Confirm that the archive is open

Sql>archive log list; 2.2. Open Library level minimum additional log

Check that the source-side database attached log is open

Sql>select supplemental_log_data_min from V$database;

Open Database Attachment Log

Sql>alter database add supplemental log data;

Toggle log for additional log to take effect

Sql>alter SYSTEM ARCHIVE LOG current;

Or

Sql>alter system switch logfile;

Confirm that the minimum additional log is open

Select Supplemental_log_data_min from V$database; 2.3. Create Goldengate proprietary users

To create a goldengate table space:

Sql>create tablespace goldengate datafile '/oracle/oracle/oradata/ora11/goldengate.dbf ' size 100m;

Note: The source-side goldengate does not create any tables in the database to store any data, so the table space gives less space to

Create goldengate users and authorize:

Sql>create user goldengate identified by goldengate default Tablespace goldengate;

Authorize to the User:

If the customer is not strict about permission management, give DBA authority directly:

Sql>grant dba to Goldengate;

If management strictly please refer to each version Ogg document for authorization

Note: DBA authority is required to open DDL 3. Source-side Deployment Goldengate 3.1. Download and upload goldengate to the server

Linux\unix to Oracle users, Windows reaches Oracle's installation user 3.2. Configure Environment Variables

The following three variables are mainly configured:

Oracle_sid oracle_home Ld_library_path (Linux environment)

Where D_library_path write ogg installation directory

Export ld_library_path=/ggs/11.0: $LD _library_path 3.3. Configure startup Mgr

Windows environment needs to be added to the service

. \install AddService

./ggsci

Create Subdirs

Configuring MGR Parameters

Edit params Mgr

Port 7809

Dynamicportlist 7840-7898

Autostart ER *

AutoRestart er *, retries 5, Waitminutes 3

Purgeoldextracts/dirdat/*,usecheckpoints, Minkeepdays 7

Start Mgr

Start Mgr

Confirm Mgr started successfully

Info all 3.4. Add Trandata

Ggsci Log on to the database

dblogin userid Goldengate,password goldengate

Increase Trandata

Add Trandata Ajdbo.passenger

Confirm Trandata added successfully

Info Trandata Ajdbo.passenger

To confirm a table in the database that has been added Trandata

SELECT * from dba_log_groups where owner= ' xxx ' and table_name= ' xxx '; 3.5. Configure, start the extract process

Increase the Extract process

Add extract ext_ynkg, Tranlog, begin now

Increase Exttrail

Add Exttrail./dirdat/ld,extract ext_ynkg, megabytes 100

Configuring the Extract Process

Edit params ext_ynkg

EXTRACT ext_ynkg

Setenv (Nls_lang=american_america. ZHS16GBK)

USERID goldengate, PASSWORD goldengate

Tranlogoptions Excludeuser Goldengate

Exttrail./dirdat/ld

--gettruncates

--getupdatebefores

Eofdelay 3

Numfiles 5000

Discardfile/DIRRPT/EXT_YNKG.DSC, APPEND, megabytes 1000

Dynamicresolution

Table Ajdbo.passenger;

Talle Ajdbo.channel;

Table Ajdbo.validate_info;

Table Ajdbo.bord_info;

Start the Extract process

Start ext_ynkg

Confirm that the process started successfully

Info ext_ynkg 3.6. Get benchmark SCN 3.6.1. Method One

This approach can be used if the customer environment archive is more reserved:

First confirm the start time of the earliest transaction in the system

sql> Select min (start_time) from Gv$transaction;

MIN (start_time)

--------------------

02/08/16 19:21:38

Then, when specifying the start time of the extract process, specify an earlier time for the extract to be extracted from here:

Add extract <ext_name> Tranlog threads 2 begin 2016-02-08 19:20:00

Finally, query the current SCN number

Select Current_scn from V$database;

The SCN is the baseline SCN (SCN3), such as the method of importing exports, the SCN is the final SCN point of time, and if Rman is used, the SCN of the final Rman recovery is greater than or equal to this SCN. 3.6.2. Method Two

This approach can be used if the customer environment archive is kept relatively low

Querying the current SCN number (SCN1)

Select Current_scn from V$database;

View the SCN number (SCN2) of the oldest transaction in the current database system

Select min (START_SCN) from Gv$transaction;

The start time of the earliest transaction in the system is queried multiple times until this value is greater than SCN1. (If the big transaction has been running, consider killing the transaction corresponding process, find the statement for the process as follows:

Select P.INST_ID,P.SPID,T.START_SCN

From Gv$transaction t,gv$process p,gv$session s

where S.taddr=t.addr and P.addr=s.paddr and t.start_scn<=&1;)

Requery the current SCN number (SCN3)

Select Current_scn from V$database;

The SCN is the baseline SCN (SCN3), such as the method of importing exports, the SCN is the final SCN point of time, and if Rman is used, the SCN of the final Rman recovery is greater than or equal to this SCN. 3.7. Archive current log (optional)

This step is only required to use Rman for initialization and use of the previous full standby, in order to copy past archives to revert to the previously identified SCN.

Query Current log number:

Select Inst_id,group#,sequence#,archived,status from Gv$log;

Archive Current log:

ALTER SYSTEM ARCHIVE LOG current;

Then confirm that the sequence for current is archived for the first time.

Select inst_id,sequence#,archived from Gv$archived_log where sequence#=&1;

Note: Two nodes in the RAC environment must be validated 3.8. Configure the pump process

Increase the pump process

Add extract pmp_ynkg, Exttrailsource./dirdat/ld

Add Rmttail File

Add Rmttrail./dirdat/rd,extract pmp_ynkg,megabytes 100

Configuring the Pump Process

Edit params pmp_ynkg

EXTRACT pmp_ynkg

USERID Goldengate,password Goldengate

PassThru

Rmthost 10.1.0.50,mgrport 7809

Rmttrail./dirdat/rd

--report at 01:59

--reportrollover at 02:00

Discardfile/DIRRPT/PMP_YNKG.DSC, APPEND, megabytes 1000

--purgeoldextracts/dirdat/*,usecheckpoints, Minkeepdays 7

Dynamicresolution

Table ajdbo.*; 4. Data initialization 4.1. Import Export mode 4.1.1. Determine the character set of both ends

Query at both ends of the character set:

Select Userenv (' language ') from dual;

No problem if the same

If different

(1) Need to use the Csscan tool to detect the source data, to see if the data imported to the target side will appear after the lack of word length, garbled and so on. The Csscan use method is shown in appendix.

(2) The need to set the environment variable Nls_lang in the extract process and the REPLICAT process is consistent with the source-side database character set. Specific whether will appear garbled also need to test. 4.1.2. Source-Side export destination table

Create directory

Create directory OGGD as '/home/oracle ';

Export the target table, FLASHBACK_SCN to the previously determined SCN3

EXPDP xpadb/xpadb directory=oggd dumpfile=xpadb. ' Date ' +%y%m%d_%h%m '. dmp.%u logfile=xpadb. ' Date ' +%Y%m%d_%H%M '. log Parallel=4 flashback_scn=xxxxx 4.1.3. Adjust target side job_queue_processes

Adjust job_queue_processes to 0

Sql>show parameter job (recorded and adjusted back later)

Sql>alter system set job_queue_processes=0 scope=memory;

Confirm that the modification was successful again.

Sql>show parameter Job 4.1.4. Target-side database import

IMPDP xpadrpt/xpadrpt directory=oggd dumpfile=xpadb.20130620_1525.dmp.%u logfile=impdp.xpadb.20160220_1525.log Parallel=4 remap_schema=xpadb:xpadrpt Remap_tablespace=xpaddat:xpaddata

4.2. Rman method 4.2.1. Back up the database

Backup database, archive, control file:

$rman Target/

Run {

Allocate channel C1 type disk;

Allocate channel C2 type disk;

Allocate channel C3 type disk;

Allocate channel C4 type disk;

Crosscheck backupset;

Delete noprompt expired backupset;

Backup as Backupset full database tag= ' db_bak ' format '/u01/backup/db_%u_%t ';

SQL ' alter system archive log current ';

Backup Archivelog all tag= ' Arc_bak ' format= '/u01/backup/arc_%u_%t ' Filesperset 6;

Backup current Controlfile tag= ' Ctl_bak ' format= '/u01/backup/ctl_file_%u_%t ';

Release channel C1;

Release channel C2;

Release channel C3;

Release channel C4;

4.2.2. Determine the final SCN

The database that was just backed up on the source side is executed:

Sql>select group#, thread#,sequence#,archived,status,first_change# from Gv$log;

group# thread# sequence# ARC STATUS first_change#

---------- ---------- ---------- --- ---------------- -------------

1 1 YES INACTIVE 1697423

3 1 241 NO Current 1718793

2 1 239 YES INACTIVE 1690905

Gets the last SCN number of an inactive archived log, if there are multiple archived inactive groups, take the largest first_change#, take 1697423, and the SCN number (SCN4) is the SCN number for the final Rman recovery and start Replicat. (This SCN4 must be larger than the benchmark SCN (SCN3))

Note: If the RAC environment is to ensure that this SCN number corresponds to the log both sides have been archived. 4.2.3. Copy backup, PFILE, password file to target 4.2.4. Target-Side Execution recovery

Start Database to Nomount state

Sqlplus '/As Sysdba '

Sql>startup Nomount

Start Rman Recovery control file

Rman Target/

Rman>restore controlfile from '/u01/backup/ctl_file_757366280381.dbf ';

Change database to Mount state

Sqlplus '/As Sysdba '

Sql>alter database Mount;

To start the Rman recovery database

Rman Target/

Rman>restore database;

Rman>

Run

{

Set Archivelog destination to '/u01/arch ';

SET UNTIL SCN 218412; – The SCN number identified at the front SCN4

RECOVER DATABASE;

4.2.5. Adjust target side job_queue_processes

Adjust job_queue_processes to 0

Sql>show parameter job (recorded and adjusted back later)

Sql>alter system set job_queue_processes=0 scope=memory;

Confirm that the modification was successful again.

Sql>show parameter Job

4.2.6. Open database on target side

Open the Library

Sql>alter database open resetlogs; 5. Target-side database configuration 5.1. Disable all Job 5.1.1. Dbms_job

Disable Job

Declare

V_sql number;

CURSOR C_job is SELECT job from

Dba_jobs where Schema_user in

(' JP ');

BEGIN

OPEN C_job;

LOOP

FETCH c_job into V_sql;

EXIT when C_job%notfound;

Dbms_ijob.broken (v_sql,true);

End Loop;

Close C_job;

Commit

End

/

Verify that the job is really disabled

Select Job,log_user,priv_user,schema_user,broken from Dba_jobs where Schema_user in (' JP '); 5.1.2. Schedule Job

Disable Job

Declare

V_sql VARCHAR2 (200);

CURSOR c_job is SELECT owner| | '. ' | | Job_name from

Dba_scheduler_jobs where OWNER in

(' JP ');

BEGIN

OPEN C_job;

LOOP

FETCH c_job into V_sql;

EXIT when C_job%notfound;

Dbms_scheduler.disable (NAME =>v_sql);

End Loop;

Close C_job;

Commit

End

/

Verify that the job is really disabled

SELECT owner,job_name,state from Dba_scheduler_jobs WHERE OWNER in (' JP '); 5.1.3. Adjust target end job_queue_processes

Adjust the target-side job_queue_processes value back to the original value 5.2. Disable all trigger

Close Trigger

Declare

V_sql VARCHAR2 (2000);

CURSOR C_trigger is SELECT ' alter TRIGGER ' | | owner| | '. ' | | trigger_name| | ' Disable ' from

Dba_triggers where owner in

(' JP ');

BEGIN

OPEN C_trigger;

LOOP

FETCH C_trigger into V_sql;

EXIT when C_trigger%notfound;

Execute immediate v_sql;

End Loop;

Close C_trigger;

End

/

Confirm that trigger is all closed

Select distinct status from Dba_triggers where owner in (' JP '); 5.3. Disable all foreign keys

disabling foreign keys

Declare

V_sql VARCHAR2 (2000);

CURSOR C_con is SELECT ' ALTER TABLE ' | | owner| | '. ' | | table_name| | ' Disable constraint

'|| Constraint_name from dba_constraints where constraint_type= ' R ' and owner in

(' JP ');

BEGIN

OPEN C_con;

LOOP

FETCH C_con into V_sql;

EXIT when C_con%notfound;

Execute immediate v_sql;

End Loop;

Close C_con;

End

/

Confirm that the foreign key has been disabled

Select Owner,constraint_name,constraint_type,status

From Dba_constraints

where constraint_type= ' R ' and status = ' ENABLED ' and owner in (' JP '); 5.4. Make sure the primary key of target table is available

Select T1. Status,t1. Validated,t2.status,t1.constraint_name,t1.owner

From Dba_constraints t1,dba_objects T2

where T2. Object_name=t1.constraint_name and T1. OWNER in (' JP ');

Confirm Query Results:

T1. Status is enabled

T1. Validated for validated

T2.status for valid

If it is not, it needs to be changed to open state. 6. Target-side deployment Goldengate 6.1. Download and upload goldengate to the server

Linux\unix to Oracle users, Windows reaches Oracle's installation user 6.2. Configure Environment Variables

The following three variables are mainly configured:

Oracle_sid oracle_home Ld_library_path (Linux environment)

Where D_library_path write ogg installation directory

Export ld_library_path=/ggs/11.0: $LD _library_path 6.3. Configure Startup Mgr

Windows environment needs to be added to the service

. \install AddService