Shulou(Shulou.com)06/01 Report--

Objective: this blog shows that during the construction of 11.2.0.4 oracle database ogg, there are two ways to prepare the database: the first is that the main database rman is fully backed up and restored, and the second is that expdp exports users from the main database and imports them in the standby database.

The test of ogg data synchronization mainly focuses on the semi-synchronous data similar to mysql, that is, the standby database synchronizes part of the data from the main database.

?

1. Basic information about ogg servers

Configuration

Mainframe

Source end

Target end

Hostnam

Ogg1

Ogg2

IP address

10.117.130.231

10.117.130.232

Memory

3832MB

3832MB

Database management user

Uid=500 (oracle) gid=601 (oinstall) group = 601 (oinstall), 603 (dba)

Uid=500 (oracle) gid=601 (oinstall) group = 601 (oinstall), 603 (dba)

Database version

11.2.0.4

11.2.0.4

ORACLE_HOME

/ u01/oracle/app/oracle/product/11.2.0.4/db

/ u01/oracle/app/oracle/product/11.2.0.4/db

NIL_LANG

AMERICAN_AMERICA.ZHS16GBK

AMERICAN_AMERICA.ZHS16GBK

OGG_HOME

/ u01/oracle/app/ogg

/ u01/oracle/app/ogg

OGG version

12.3.0.1.0

12.3.0.1.0

OGG administrative user / password

GOLDENGATE/GOLDENGATE

GOLDENGATE/GOLDENGATE

Users synchronized with OGG

HR (sample base), ZHUL (analog business)

II. Adjustment of the main library

1. The archive mode is enabled in the main library.

Select log_mode from v$database

If the archive mode is not enabled in the main library, the archive mode is enabled.

2. Open the database force-logging and supplementary log

Alter database force logging

Alter database add supplemental log data

Alter database add supplemental log data (primary key) columns

3. Deal with compressed tables and partitioned compressed tables and turn them into uncompressed tables

Select 'alter table' | | table_owner | |'. | | table_name | | 'move partition' | | partition_name | 'nocompress update indexes;' from dba_tab_partitions where compression =' ENABLED' and table_owner='HR' |

4. Process the table of nologging and change it to the table of logging

Select 'alter table' | | owner | |'. | | table_name | | 'logging;' from dba_tables where owner in (' HR') and logging='NO'

5. Deal with nologging partition table as logging partition

Select distinct 'alter table' | | table_owner | |'. | | table_name | | 'logging;' from dba_tab_partitions where table_owner in (' HR') and logging='NO'

6. Create OGG dedicated users in the main library

Create tablespace goldengate datafile'$ORACLE_BASE/oradata/$ORACLE_SID/goldengate.dbf' size 100m autoextend on

CREATE USER GOLDENGATE IDENTIFIED BY GOLDENGATE DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP

GRANT CONNECT TO GOLDENGATE

GRANT ALTER ANY TABLE TO GOLDENGATE

GRANT ALTER SESSION TO GOLDENGATE

GRANT CREATE SESSION TO GOLDENGATE

GRANT FLASHBACK ANY TABLE TO GOLDENGATE

GRANT SELECT ANY DICTIONARY TO GOLDENGATE

GRANT SELECT ANY TABLE TO GOLDENGATE

GRANT RESOURCE TO GOLDENGATE

GRANT SELECT ANY TRANSACTION TO GOLDENGATE

IV. Backup of the source data of the main database

1. Rman full backup

Backup database format'/ home/oracle/rman/full_%D_%d_%U.bak'

Select current_scn from v$database

Alter system switch logfile

Backup archivelog all format'/ home/oracle/rman/arch_%D_%d_%U.bak'

2. Expdp is exported according to the scheme

2.1 Test data sources

[oracle@ogg1 ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 26 14:28:36 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > create user zhul identified by zhul default tablespace users

User created.

SQL > grant create session to zhul

Grant succeeded.

SQL > grant resource to zhul

Grant succeeded.

SQL > create table zhul.emp as select * from scott.emp

Table created.

SQL > c/emp/dept

1 * create table zhul.dept as select * from scott.emp

SQL > c/emp/dept

1 * create table zhul.dept as select * from scott.dept

SQL > /

Table created.

SQL > c/dept/SALGRADE

1 * create table zhul.SALGRADE as select * from scott.dept

SQL > c/dept/SALGRADE

1 * create table zhul.SALGRADE as select * from scott.SALGRADE

SQL > /

Table created.

SQL > create directory zhul as'/ home/oracle/'

Directory created.

SQL >

SQL > select current_scn from v$database

CURRENT_SCN

-

1057694

2.2 expdp backup

[oracle@ogg1 ~] $cat param

Userid=zhul/zhul

Directory=zhul

Tables=emp

Dumpfile=expdp_zhul_emp_270926.dmp

Logfile=log_zhul_emp_270926.log

Flashback_scn=1057694

Query=emp: "where hiredate > '1982-01-02'"

[oracle@ogg1 ~] $

[oracle@ogg1 ~] $expdp parfile=/home/oracle/param

Export: Release 11.2.0.4.0-Production on Tue Sep 26 14:54:27 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ZHUL". "SYS_EXPORT_TABLE_01": zhul/* parfile=/home/oracle/param

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "ZHUL". "EMP" 8.109 KB 3 rows

Master table "ZHUL". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for ZHUL.SYS_EXPORT_TABLE_01 is:

/ home/oracle/expdp_zhul_emp_270926.dmp

Job "ZHUL". "SYS_EXPORT_TABLE_01" successfully completed at Tue Sep 26 14:54:30 2017 elapsed 0 00:00:01

[oracle@ogg1 ~] $

Fifth, prepare the database and restore the target data

Preparation before recovery

If flashback is enabled in the source database, you need to create an equivalent flashback directory, that is, $ORACLE_BASE/ fast_recovery_area, otherwise an error will be reported:

You need to establish the same audit directory as the main database, otherwise an error will be reported:

Mkdir-p / u01/oracle/app/oracle/admin/orcl/adump

Create a data directory for the standby library:

Mkdir-p / u01/oracle/app/oracle/oradata/orcl/

1. Rman full recovery

Rman target /

RMAN > startup nomount

RMAN > restore spfile from'/ home/oracle/rman/full_25_ORCL_02sfc009_1_1.bak'

RMAN > shutdown immediate

RMAN > startup nomount

RMAN > restore controlfile from'/ home/oracle/rman/full_25_ORCL_02sfc009_1_1.bak'

RMAN > alter database mount

RMAN > restore database

RMAN > catalog start with'/ home/oracle/rman'

RMAN > run {

Set until scn 996247

Recover database

}

-- the SCN number here is the SCN number found during the backup of the source data of the main database, which is found during the execution of the green sql command in part IV. Be sure to have it.

2. Prepare expdp backup and import

2.1Create directory

Create directory zhul as'/ home/oracle'

2.2 create Ogg dedicated users

CREATE USER GOLDENGATE IDENTIFIED BY GOLDENGATE DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP

GRANT CONNECT TO GOLDENGATE

GRANT ALTER ANY TABLE TO GOLDENGATE

GRANT ALTER SESSION TO GOLDENGATE

GRANT CREATE SESSION TO GOLDENGATE

GRANT FLASHBACK ANY TABLE TO GOLDENGATE

GRANT SELECT ANY DICTIONARY TO GOLDENGATE

GRANT SELECT ANY TABLE TO GOLDENGATE

GRANT RESOURCE TO GOLDENGATE

GRANT SELECT ANY TRANSACTION TO GOLDENGATE

2.3 data Import

[oracle@ogg2 ~] $impdp system/oracleoracle directory=zhul tables=zhul.emp dumpfile=expdp_zhul_emp_270926.dmp logfile=impdp_zhul_emp_270926.log

Import: Release 11.2.0.4.0-Production on Tue Sep 26 15:08:09 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM". "SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM". "SYS_IMPORT_TABLE_01": system/* directory=zhul tables=zhul.emp dumpfile=expdp_zhul_emp_270926.dmp logfile=impdp_zhul_emp_270926.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . Imported "ZHUL". "EMP" 8.109 KB 3 rows

Job "SYSTEM". "SYS_IMPORT_TABLE_01" successfully completed at Tue Sep 26 15:08:16 2017 elapsed 0 00:00:05

[oracle@ogg2 ~] $

6. Install OGG in the main library

1. Create an installation directory

Mkdir-p $ORACLE_BASE\ ogg

2. Upload and decompress OGG software

3. Edit the OGG silent installation script

[oracle@ogg1 response] $pwd

/ u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response

[oracle@ogg1 response] $ls

Oggcore.rsp oggcore.rsp.bak

[oracle@ogg1 response] $cat oggcore.rsp

#

# # Copyright (c) Oracle Corporation 2014. All rights reserved. # #

#

# # Specify values for the variables listed below to customize # #

# # your installation. # #

#

# # Each variable is associated with a comment. The comment # #

# # can help to populate the variables with the appropriate # #

# # values. # #

#

# # IMPORTANT NOTE: This file should be secured to have read # #

# # permission only by the oracle user or an administrator who # #

# # own this installation to protect any sensitive input values. # #

#

#

#-

# Do not change the following system generated value.

#-

Oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2

#

#

# # Oracle GoldenGate installation option and details # #

#

#

#-

# Specify the installation option.

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and

# ORA11g for installing Oracle GoldenGate for Oracle Database 11g

#-

INSTALL_OPTION=ORA11g

#-

# Specify a location to install Oracle GoldenGate

#-

SOFTWARE_LOCATION=/u01/oracle/app/ogg

#-

# Specify true to start the manager after installation.

#-

START_MANAGER=true

#-

# Specify a free port within the valid range for the manager process.

# Required only if START_MANAGER is true.

#-

MANAGER_PORT=1700

#-

# Specify the location of the Oracle Database.

# Required only if START_MANAGER is true.

#-

DATABASE_LOCATION=/u01/oracle/app/oracle/product/11.2.0.4/db

#

#

# # Specify details to Create inventory for Oracle installs # #

# # Required only for the first Oracle product install on a system. # #

#

#

#-

# Specify the location which holds the install inventory files.

# This is an optional parameter if installing on

# Windows based Operating System.

#-

INVENTORY_LOCATION=/u01/oracle/app/oraInventory

#-

# Unix group to be set for the inventory directory.

# This parameter is not applicable if installing on

# Windows based Operating System.

#-

UNIX_GROUP_NAME=oinstall

[oracle@ogg1 response] $

[oracle@ogg1 response] $

4. OGG silent installation

[oracle@ogg1 Disk1] $pwd

/ u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@ogg1 Disk1] $. / runInstaller-silent-responseFile / u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

5. Configure the view base table related to OGG on the source side

[oracle@ogg1 app] $cd ogg

[oracle@ogg1 ogg] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 25 21:25:29 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > @ marker_setup.sql

SQL > @ ddl_setup.sql

SQL > @ role_setup.sql

SQL > @ ddl_enable.sql

SQL > sequence.sql

SQL > grant GGS_GGSUSER_ROLE to GOLDENGATE

SQL > alter system set enable_goldengate_replication = true scope=both

6. Configure the OGG mgr process

Edit param mgr

PORT 1700

PURGEOLDEXTRACTS / u01/oracle/app/ogg/dirdat, USECHECKPOINTS

-- make sure the current directory is in the ogg installation directory / u01/oracle/app/ogg, and then restart the mgr process

Start mgr

7. Enable the synchronization log of source data

Dblogin userid goldengate,password goldengate

Info trandata hr.*

Add trandata hr.*

Confirm that the transfer log is on

8. Configure the source data extraction process ext_hr,ext_zhul

Add extract ext_hr,tranlog,begin now

8.1 add ext_hr data extraction data storage file

Add exttrail / u01/oracle/app/ogg/dirdat/et,extract ext_hr

8.1.1 add remote data storage files for ext_hr

ADD RMTTRAIL / u01/oracle/app/ogg/dirat/et, EXTRACT EXT_HR

Edit param ext_hr

EXTRACT ext_hr

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Userid GOLDENGATE,password GOLDENGATE

Rmthost 10.117.130.232,mgrport 1700

Rmttrail / u01/oracle/app/ogg/dirdat/et

TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE

Ddl include all

Table hr.t,SQLPREDICATE "where salary > 9000"

8.1.3 start the ext_hr process

Start ext_hr

8.1.4 increase of ogg user checkpoints

Add checkpointtable GOLDENGATE.checkpoint

8.2 the main library configuration data extraction process exp_zhul

Dblogin userid GOLDENGATE,password GOLDENGATE

View the data transfer information of the data table under zhul

Info trandata zhul.*

Enable data table transmission under zhul

Add trandata zhul.*

Add ogg data extraction process for standby database

Add extract ext_zhul,tranlog,begin now

Add ogg data extraction log queue files for standby libraries

Add exttrail / u01/oracle/app/ogg/dirdat/ez,extract ext_zhul

ADD RMTTRAIL / u01/oracle/app/ogg/dirat/ez, EXTRACT ext_zhul

Edit the data extraction process ext_zhul of the main library

Edit param ext_zhul

EXTRACT ext_zhul

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Userid GOLDENGATE,password GOLDENGATE

Rmthost 10.117.130.232,mgrport 1700

Rmttrail / u01/oracle/app/ogg/dirdat/ez

TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE

-- ddl include all

Table zhul.emp,SQLPREDICATE "where hiredate > '1982-01-02"

8.3 start the exp_zhul process

7. Install OGG at the target end

1. Create an installation directory

Mkdir-p $ORACLE_BASE\ ogg

2. Upload and decompress OGG software

3. Edit the OGG silent installation script

[oracle@ogg1 response] $pwd

/ u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response

[oracle@ogg1 response] $ls

Oggcore.rsp oggcore.rsp.bak

[oracle@ogg1 response] $cat oggcore.rsp

#

# # Copyright (c) Oracle Corporation 2014. All rights reserved. # #

#

# # Specify values for the variables listed below to customize # #

# # your installation. # #

#

# # Each variable is associated with a comment. The comment # #

# # can help to populate the variables with the appropriate # #

# # values. # #

#

# # IMPORTANT NOTE: This file should be secured to have read # #

# # permission only by the oracle user or an administrator who # #

# # own this installation to protect any sensitive input values. # #

#

#

#-

# Do not change the following system generated value.

#-

Oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2

#

# # Oracle GoldenGate installation option and details # #

#

#-

# Specify the installation option.

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and

# ORA11g for installing Oracle GoldenGate for Oracle Database 11g

#-

INSTALL_OPTION=ORA11g

#-

# Specify a location to install Oracle GoldenGate

#-

SOFTWARE_LOCATION=/u01/oracle/app/ogg

#-

# Specify true to start the manager after installation.

#-

START_MANAGER=true

#-

# Specify a free port within the valid range for the manager process.

# Required only if START_MANAGER is true.

#-

MANAGER_PORT=1700

#-

# Specify the location of the Oracle Database.

# Required only if START_MANAGER is true.

#-

DATABASE_LOCATION=/u01/oracle/app/oracle/product/11.2.0.4/db

#

# # Specify details to Create inventory for Oracle installs # #

# # Required only for the first Oracle product install on a system. # #

#

#-

# Specify the location which holds the install inventory files.

# This is an optional parameter if installing on

# Windows based Operating System.

#-

INVENTORY_LOCATION=/u01/oracle/app/oraInventory

#-

# Unix group to be set for the inventory directory.

# This parameter is not applicable if installing on

# Windows based Operating System.

#-

UNIX_GROUP_NAME=oinstall

[oracle@ogg1 response] $

4. OGG silent installation

[oracle@ogg1 Disk1] $pwd

/ u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@ogg1 Disk1] $. / runInstaller-silent-responseFile / u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

5. Configure the view base table related to OGG on the source side

[oracle@ogg2 app] $cd ogg

[oracle@ogg2 ogg] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 25 21:25:29 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > @ marker_setup.sql

SQL > @ ddl_setup.sql

SQL > @ role_setup.sql

SQL > @ ddl_enable.sql

SQL > sequence.sql

SQL > grant GGS_GGSUSER_ROLE to GOLDENGATE

SQL > alter system set enable_goldengate_replication = true scope=both

6. Configure the MGR process on the target side

Edit param mgr

PORT 1700

PURGEOLDEXTRACTS / u01/oracle/app/ogg/dirdat, USECHECKPOINTS

Start mgr

7. Configure the replication process rep_hr on the destination side

Add replicat rep_hr exttrail / u01/oracle/app/ogg/dirdat/et,checkpointtable GOLDENGATE.checkpoint

Edit param rep_hr

-- Replicat group--

Replicat rep_hr

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

-- source and target definitions

ASSUMETARGETDEFS

HANDLECOLLISIONS

-- target database login--

Userid GOLDENGATE, password GOLDENGATE

-- file for dicarded transaction--

Discardfile / u01/oracle/app/ogg/rep_hr_discard.txt, append, megabytes 10

-- ddl support DDL

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP

-Specify table mapping-

MAP hr.t, TARGET hr.t, WHERE (salary > 9000)

7.1 start the replication process rep_hr

8. (master expdp export standby Impdp import) standby library configuration replication process rep_zhul

Add replicat rep_zhul exttrail / u01/oracle/app/ogg/dirdat/ez,checkpointtable GOLDENGATE.checkpoint

View the transfer of the zhul data table of the standby database

Info trandata zhul.*

Enable the transfer of data tables under the slave database zhul

Add trandata zhul.*

Edit replication process rep_zhul parameters

Edit param rep_zhul

-- Replicat group--

Replicat rep_zhul

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

-- source and target definitions

ASSUMETARGETDEFS

HANDLECOLLISIONS

-- target database login--

Userid GOLDENGATE, password GOLDENGATE

-- file for dicarded transaction--

Discardfile / u01/oracle/app/ogg/rep_zhul_discard.txt, append, megabytes 10

-- ddl support DDL

-- DDL INCLUDE ALL

-- DDLERROR DEFAULT IGNORE RETRYOP

-Specify table mapping-

MAP zhul.emp, TARGET zhul.emp, WHERE (hiredate > '1982-01-02')

-- the red part of the test cannot start the process, otherwise the process starts an error

8.1 start the rep_zhul process

VIII. Data synchronization testing

Test for the company's business scenario, hr.t source: create table hr.t as select * from hr.employees

1. Clear the hr.t table at the source end of the main database

2. Clear the hr.t table at the target end of the database.

3. DML simulation of hr.t table on the source side of the main database

4. Check the data synchronization on the destination side in rman mode.

4.1 data statistics in accordance with salary > 9000 at the source end

Conclusion: from the point of view of data synchronization, the data synchronization strategy can be controlled on the target side.

?

5. Expdp data synchronization test

5.1 Delete a data hiredate > '1982-01-02' on the source side

5.2 the destination side views data synchronization

5.3 delete where hiredate on the source side

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.