76 道 Oracle Goldengate 面試問題

margiex發表於2017-11-20

OracleGo面試

1. Oracle Goldengate 支援部署到哪些拓撲?

GoldenGate supports the following topologies. More details can be found here.

Unidirectional
Bidirectional
Peer-to-peer
Broadcast
Consolidation
Cascasding

2. Oracle Goldengate 複製鏈路包含哪些元件?

The replication configuration consists of the following processes.

Manager
Extract
Pump
Replicate

3. Oracle GoldenGate 支援哪些資料庫?

Oracle Database
TimesTen
MySQL
IBM DB2
Microsoft SQL Server
Informix
Teradata
Sybase
Enscribe
SQL/MX

For the latest list, look here.

4. Oracle Goldengate 支援哪些事務型別的複製?

Goldengate supports both DML and DDL Replication from the source to target.

5. OGG複製中需要哪些附加日誌的開關開啟?

The following supplemental logging is required.

Database supplemental logging
Object level logging

6. OGG複製中為什麼需要附加日誌?

When a transaction is committed on the source database, only new data is written to the Redo log. However for Oracle to apply these transactions on the destination database, the before image key values are required to identify the effected rows. This data is also placed in the trail file and used to identify the rows on the destination, using the key value the transactions are executed against them.

7. 雙向複製中，要考慮哪些因素?

The customer should consider the following points in an active-active replication environment.

Primary Key: Helps to identify conflicts and Resolve them.
Sequences: Are not supported. The work around is use to use odd/even, range or concatenate sequences.
Triggers: These should be disabled or suppressed to avoid using uniqueness issue
Data Looping: This can easy avoided using OGG itself
LAG: This should be minimized. If a customer says that there will not be any LAG due to network or huge load, then we don’t need to deploy CRDs. But this is not the case always as there would be some LAG and these can cause Conflicts.
CDR (Conflict Detection & Resolution): OGG has built in CDRs for all kind of DMLs that can be used to detect and resolve them.
Packaged Application: These are not supported as it may contain data types which are not support by OGG or it might not allow the application modification to work with OGG.

8. OGG 可執行程式是否支援部署在ASM Cluster File System (ACFS)?

Yes, you can install and configure OGG on ACFS.

9. OGG可執行程式是否支援部署在Database File System (DBFS)? 哪些檔案可以部署在DBFS?

No, OGG binaries are not supported on DBFS. You can however store parameter files, data files (trail files), and checkpoint files on DBFS.

10. GLOBALS 檔案預設的存放位置是哪?

A GLOBALS file is located under Oracle GoldenGate installation directory (OGG HOME)

11. 欄位資料過濾可以在哪些程式裡配置?

Filtering of the columns of a table can be set at the Extract, Pump or Replicat level.

12. OGG複製過程中，傳輸程式是否必須要配置?

A PUMP extract is an option, but it is highly recommended to use this to safe guard against network failures. Normally it is configured when you are setting up OGG replication across the network.

13. 經典抽取和整合整合的主要區別是什麼?

經典抽取:

The Classic Capture mode is the traditional Extract process that accesses the database redo logs (optionally archive logs) to capture the DML changes occurring on the objects specified in the parameter files.
At the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs.
This capture mode is available for other RDBMS as well.
There are some data types that are not supported in Classic Capture mode.
Classic capture can’t read data from the compressed tables/tablespaces.

整合抽取 (IC):

In the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
This feature is only available for oracle databases in Version 11.2.0.3 or higher.
It also supports various object types which were previously not supported by Classic Capture.
This Capture mode supports extracting data from source databases using compression.
Integrated Capture can be configured in an online or downstream mode.

14. 列出可執行的抽取程式需要的最小引數配置?

The following are the minimium required parameters which must be defined in the extract parameter file.

EXTRACT NAME
USERID
EXTTRAIL
TABLE

15. 巨集是什麼?

Macro is an easier way to build your parameter file. Once a macro is written it can be called from different parameter files. Common parameters like username/password and other parameters can be included in these macros. A macro can either be another parameter file or a library.

16. 哪些地方可以呼叫巨集?

The macros can be called from the following parameter files.

Manager
Extract
Replicat
Gobals

17. 如何定義一個巨集?

A macro statement consists of the following.

Name of the Macro
Parameter list
Macro body

示例:
MACRO #macro_name
PARAMS (#param1, #param2, …)
BEGIN
< macro_body >
END;

18. 我想多個程式往一個佇列檔案中寫入資料，是否可行?

Only one Extract process can write to one exttrail at a time. So you can’t configure multiple extracts to write to the same exttrail.

19. Oracle Goldengate可以加密哪些資料?

Oracle Goldengate provides 3 types of Encryption.

Data Encryption using Blow fish.
Password Encryption.
Network Encryption.

20. OGG提供哪些密碼加密選項?

You can encrypt a password in OGG using

Blowfish algorithm and
Advance Encryption Standard (AES) algorithm

21. AES加密提供哪些加密強度?

You can encrypt the password/data using the AES in three different keys

a) 128 bit
b) 192 bit and
c) 256 bit

22. OGG是否提供相應的方法檢查OGG的引數語法，而不用通過實際執行?

Yes, you can place the SHOWSYNTAX parameter in the parameter file and try starting. If there is any error you will see it.

23. 如何在解析日誌時，增加緩衝大小，以儲存更多交易資料?

If you are using the Classical Extract you may use the TRANSLOGOPTION ASMBUFSIZE parameter to control the read size for ASM Databases.

24. 在discard檔案中，可以看到哪些資訊?

When data is discarded, the discard file can contain:
1. Discard row details
2. Database Errors
3. Trail file number

25. 使用什麼命令可以切換程式寫一個新的佇列檔案?

You can use the following command to write the trail data to a new trail file.
SEND EXTRACT ext_name, ROLLOVER

26. 如何知道引數檔案最近是否被修改過?

When ever a process is started, the parameters in the .prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters.

==================GoldenGate 12c關鍵新特性====================

27. GoldenGate 12c關鍵新特性有哪些?

The following are some of the more interesting features of Oracle GoldenGate 12c:

Support for Multitenant Database
Coordinated Replicat
Integrated Replicat Mode
Use of Credential store
Use of Wallet and master key
Trigger-less DDL replication
Automatically adjusts threads when RAC node failure/start
Supports RAC PDML Distributed transaction
RMAN Support for mined archive logs

28. OGG 12c安裝時有哪些選項?

You can install Oracle GoldenGate 12c using in 2 ways:

1) Interactive Installation with OUI – Graphical interface
2) Silent Installation with OUI – Command Interface

29. OGG 12c中的Credential Store是什麼?

OGG Crendential Store manages Encrypted Passwords and USERIDs that are used to interact with the local database and Associate them with an Alias. Instead of specifying actual USERID and Password in a command or a parameter file, you can use an alias. The Credential Store is implemented as an autologin wallet within the Oracle Credential Store Framework (CSF).

30. OGG12c如何配置Credentail Store?

Steps to configure Oracle Credential Store are as follows:

1) By Default Credential Store is is located under “dircrd” directory.
If you want to specify a different location use can specidy “CREDENTIALSTORELOCATION” parameter in GLOBALS file.
Example: CREDENTIALSTORELOCATION /u01/app/oracle/OGG_PASSWD
2) Goto OGG home and connect to GGSCI.
cd $OGG_HOME
./ggsci
GGSCI>

31. 使用什麼命令建立credential store?

ADD CREDENTIALSTORE

32. 如何新增授權資訊到credential store?

ALTER CREDENTIALSTORE ADD USER userid,
[PASSWORD password]
[ALIAS alias]
[DOMAIN domain]
Example: GGSCI> ALTER CREDENTIALSTORE ADD USER GGS@orcl, PASSWORD oracle ALIAS extorcl DOMAIN Oracle GoldenGate

33. 如何從Oracle Credential Store中獲取資訊?

GGSCI> INFO CREDENTIALSTORE
OR
GGSCI> INFO CREDENTIALSTORE DOMAIN OracleGoldenGate

34. OGG 12c中有哪些不同的資料加密方法?

In OGG 12c you can encrypt data with the following 2 methods:

1) Encrypt Data with Master Key and Wallet
2) Encrypt Data with ENCKEYS

35. 在 Oracle database 11.2.0.4中如何啟用OGG複製?

The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicat.

To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.

ENABLE_GOLDENGATE_REPLICATION=true

36. 協作模式下，投遞程式是怎樣工作的?

In a Coordinated Mode Replicat operates as follows:

Reads the Oracle GoldenGate trail.
Performs data filtering, mapping, and conversion.
Constructs SQL statements that represent source database DML or DDL transactions (in committed order).
Applies the SQL to the target through the SQL interface that is supported for the given target database, such as ODBC or the native database interface.

37. 經典投遞和協作投遞（Coordinated Replicat）的主要區別是什麼?

The difference between classic mode and coordinated mode is that Replicat is multi-threaded in coordinated mode. Within a single Replicat instance, multiple threads read the trail independently and apply transactions in parallel. Each thread handles all of the filtering, mapping, conversion, SQL construction, and error handling for its assigned workload. A coordinator thread coordinates the transactions across threads to account for dependencies among the threads.

38. OGG 12c中如何建立一個協作投遞(COORDINATED REPLICATE )程式?

You can create the COORDINATED REPLICATE with the following OGG Command:

ADD REPLICAT rfin, COORDINATED MAXTHREADS 50, EXTTRAIL dirdat/et

39. 在OGG 12c中建立一個投遞程式，未指定 DISCARDFILE 引數，會怎樣?

Starting with OGG 12c, if you don’t specify a DISCARDFILE OGG process now generates a dicard file with default values whenever a process is started with START command through GGSCI.

40. 是否可以讓抽取程式在指定的CSN啟動?

Yes, Starting with OGG 12c you can now start Extract at a specific CSN in the transaction log or trail.
Example:
START EXTRACT fin ATCSN 12345
START EXTRACT finance AFTERCSN 67890

41. 能否解釋一下針對Oracle 12c投遞時的並行機制?

Yes. The database parallel process are leveraged on the target database for automatic dependency aware parallel apply.

This key enhancement makes it very easy to maintain throughput and reducing latency. Previously the Replicat process had to manually be broken down into multiple threads.

42. OGG12c的整合交付中，讀取佇列檔案的步驟有什麼變化?

The trail generated by the extract process is read by Integrated Delivery and Logical Chase Records (LCR) are created. These LCR’S are then shipped over the network to the destination database.

43. 整合投遞和協作投遞（Coordinated delivery）的區別是什麼?

Integrated delivery is the new 12c mechanism of sending extract trail to the destination in an Oracle enviornment. Coordinated delivery is the new mechanism to send data between now-Oracle databases.

=========================performance======================

44. 嘗試列出一些可以提升 OGG投遞效能的引數?

The parameters below can be used to improve the replicat performance:

BATCHSQL
GROUPTRANSOPS
INSERTAPPEND

45. Oracle Goldengate 複製時，需要監控哪些內容?

The lag and checkpoint latency of the Extract, pump and Replicat processes are normally monitored.

46. 什麼情況下使用PASSTHRU模式?

In pass-through mode, the Extract process does not look up the table definitions, either from the database or from a data definitions file. This increases the throughput of the data pump, as the object definition look-up is bypassed.

47. 什麼情況下，會造成抽取或傳輸程式變慢?

Some of the possible reasons are:

Long running batch transactions on a table.
Insufficient memory on the Extract side. Uncommitted, long running transactions can cause writing of a transaction to a temporary area (dirtmp) on disk. Once the transaction is committed it is read from the temporary location on the file system and converted to trail files.
Slow or overburdened Network.

48. 造成交付程式慢的主要原因有哪些?

Some of the possible reasons are:

Large amount of transactions on a particular table.
Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing.
If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned.
For slow Replicat’s, latency may be due to missing indexes on target.
Replicat having to process Update, delete of rows in very large tables.

49. 抽取程式正常執行了很長時間，突然一下當掉了，一小時之後我重啟了這個程式. 在這1小時內，資料庫中提交的事務會怎樣處理?

OGG checkpoint provides the fault tolerance and make sure that the transaction marked for committed is capture and captured only once. Even if the extract went down abnormally, when you start the process again it reads the checkpoint file to provide the read consistency and transaction recovery.

50. 使用整合抽取模式，並且使用了預設引數，當源端資料量增加時，抽取程式的延時超過1小時甚至更多，而且資料庫的效能也在降低。如何解決這種效能問題?

When operating in integrated capture mode, you must make sure that you have assigned sufficient memory to STREAMS_POOL_SIZE. An undersized STREAMS_POOL_SIZE or limiting the streams pool to use a specific amount of memory can cause troubles.

The best practice is to allocate STREAMS_POOL_SIZE at the instance level and allocate the MAX. SGA at GG process level as below:
SQL> alter system set STREAMS_POOL_SIZE=3G

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4)

51. 為什麼需要在交付程式中對錶進行拆分投遞，應該如何做?

In OGG you can configure replicat at the data at the schema level or at the table level using TABLE parameter of extract and MAP parameter of replicat.

For replicating the entire database you can list all the schemas in the database in the extract/replicat parameter file.

Depending the amount of redo generation you can split the tables in a schema in multiple extracts and replicats to improve the performance of data replication. Alternatively youcan also group a set of tables in the confiuration by the application functionality.

Alternatively you may need to remove tables which have long running transactions in a seperate extract process to eliminte lag on the other tables.

Let’s say that you have a schema named SCOTT and it has 100 hundred tables.

Out of these hundred tables, 50 tables are heavily utilized by application.

To improve the overall replication performance you create 3 extract and 3 replicats as follows:

Ext_1/Rep_1 –> 25 tables
Ext_2/Rep_2 –> 25 tables
Ext_3/Rep_3 –> 50 tables
Ext_1/Rep_1 and Ext_2/Rep_2 contains 25 table each which are heavily utilized or generate more redo.
Ext_3/Rep_3 contains all the other 50 tables which are least used.

==================troubleshoot=================

52. 如果才能在日誌檔案中檢視長事務?

The WARNLONGTRANS parameter can be specified with a threshold time that a transaction can be open before Extract writes a warning message to the ggs error log.

Example: WARNLONGTRANS 1h, CHECKINTERVAL 10m

53. 使用什麼命令可以檢視抽取程式的讀取點資訊?

Use the following command to view the Extract checkpoint information.
GGSCI> info extract , showch
GGSCI> info extract ext_fin, showch

54. RESTARTCOLLISION 引數與 HANDLECOLLISIONS 的區別是什麼?

The RESTARTCOLLISION parameter is used to skip ONE transaction only in a situation when the GoldenGate process crashed and performed an operation (INSERT, UPDATE & DELETE) in the database but could not checkpoint the process information to the checkpoint file/table. On recovery it will skip the transaction and AUTOMATICALLY continue to the next operation in the trail file.

When using HANDLECOLLISION GoldenGate will continue to overwritten and process transactions until the parameter is removed from the parameter files and the processes restarted.

55. 如何檢視抽取程式從日誌中捕獲的資料?

The logdump utility is used to open the trail files and look at the actual records that have been extracted from the redo or the archive log files.

56. 在配置了OGG的DB主機中，如果提示RMAN-08147 警告資訊，是怎麼回事?

This occurs when the V$ARCHIVED_LOG.NEXT_CHANGE# is greater than the SCN required by the GoldenGate Capture process and RMAN is trying to delete the archived logs. The RMAN-08147 error is raised when RMAN tries to delete these files.

When the database is open it uses the DBA_CAPTURE values to determine the log files required for mining. However if the database is in the mount state the V$ARCHIVED_LOG. NEXT_CHANGE# value is used.

See MetaLink note: 1581365.1

57. 如何使用logdump檢視已經加密過的佇列檔案?

You must use the DECRYPT option before viewing data in the Trail data.

58. 列出一些Logdump中常用的命令，並且可以搜尋佇列檔案中的資料.

Below are few logdump commands used on a daily basis for displaying or analyzing data stored in a trail file.

$ ./logdump – to connect to the logdump prompt
logdump> open /u01/app/oracle/dirdat/et000001 – to open a trail file in logdump
logdump> fileheader on – to view the trail file header
logdump> ghdr on – to view the record header with data
logdump> detail on – to view column information
logdump> detail data – to display HEX and ASCII data values to the column list
logdump> reclen 200 – to control how much record data is displayed
logdump> pos 0 – To go to the first record
logdump> next (or simply n) – to move from one record to another in sequence
logdump> count – counting records in a trail

=======================其它===================

59. 為什麼有必要把OGG抽取程式升級成整合抽取模式?

Oracle is able to provide faster integration of the new database features by moving the GoldenGate Extraction processes into the database. Due to this, the GoldenGate Integrated Extract has a number of features like Compression which are not supported in the traditional Extract. You can read more about how to upgrade to Integrated Extract and more about Integrated Delivery. Going forward preference should be give to create new extracts as Integrated Extracts and also to upgrade existing traditional Extracts.

60. 整合投遞的目標資料庫最低版本要求是多少?

Oracle 11.2.0.4 is the the minimum required database version which supports both Integrated extract and Integrated Reaplicat.

61. GoldenGate 整合投遞支援哪些資料庫?

Oracle Integrated Delivery is only available for Oracle Databases.

62. 使用整合投遞, 在哪裡可以檢視效能資料統計?

Yes with 12c, performance statistics are collected in the AWR repository and the data is available via the normal AWR reports.

63. 新增一張新的抽取取到OGG複製鏈路中的步驟是怎樣的?

The steps to be executed would be the following:

Include the new table to the Extract & pump process.
Obtain starting database SCN and Copy the source table data to the target database
Start Replicat on target at the source SCN database point.

64. GoldenGate中的 CSN號，相當於Oracle 資料庫中的什麼物件?

It is equivalent of the Oracle database SCN transaction number.

65. 如何使用抽取程式生成一個CSV檔案?

You will have to use the CSV Flat File Adaptor to create CSV files. The source would be the extract trail files which use the configuration of the adaptor settings to generate CSV files.

66. OGG安裝目錄下的DEFGEN程式是做什麼用的?

When the source and the target schema objects are not the same (different DDL’s) the Replicat process needs to know the source definition of the objects. The output from the DEFGEN utility is used in conjunction with the trail data to determine which column value in the trail belongs to which column.

67. 我們的資料庫中有壓縮表，做OGG單向同步，需要注意哪些事項.

You must use OGG 11.2 and configure GoldenGate Integrated Capture process to extract data from compressed tables.

Note: Pre OGG 11.2 doesn’t support extracting data from compressed tables

68. 當前資料庫是Oracle database 10.2.0.4,想使用整合抽取模式，應該如何做才能實現.

Oracle GoldenGate Integrated Capture process supports Oracle databases 10.2 and higher. But if you are running Oracle database 10.2 and want to you Oracle GoldenGate Integrated Capture process then you must configure downstream topology.

69. 現在我需要從一個非exadata平臺遷移到exadata，源端使用的是OGG 11.1,目標端是OGG 11.2，這種環境中如何配置OGG程式?

It is recommended that all instances of Oracle GoldenGate be the same version to take advantage of the new functionality, but this is not possible all the time and is not required. In this scenario, OGG provides a parameter called ‘FORMAT RELEASE’ which allows customers to use different versions of Oracle GoldenGate Extract, trail files, and Replicat together.

Example: RmtTrail /u01/app/oracle/dirdat, Format Release 11.1

Note: The input and output trails of a data pump must have the same trail file version.

70. 使用OGG 初始化的方法有哪些?

OGG has 2 functionalities, one it is used for Online data Replication and second for Initial Loading.

If you are replicating data between 2 homogeneous databases then the best method is to use database specific method (Exp/Imp, RMAN, Transportable tablespaces, Physical Standby and so on). Database specific methods are usually faster than the other methods.

—If you are replicating data between 2 heterogeneous databases or your replicat involves complex transformations, then the database specific method can’t be used. In those cases you can always use Oracle GoldenGate to perform initial load.

Within Oracle GoldenGate you have 4 different ways to perform initial load.

Direct Load – Faster but doesn’t support LOB data types (12c include support for LOB)
Direct Bulk Load – Uses SQL*LOAD API for Oracle and SSIS for MS SQL SERVER
File to replicat – Fast but the rmtfile limit is 2GB. If the table can’t be fit in 1 rmtfile you can use maxfiles but the replicat need to be registered on the target OGG home to read the rmtfiles from source.
File to Database utility – depending on the target database, use SQL*LOAD for Oracle and SSIS for MS SQL SERVER and so on.

Oracle GoldenGate initial loading reads data directly from the source database tables without locking them. So you don’t need downtime but it will use database resources and can cause performance issues. Take extra precaution to perform the initial load during the non-peak time so that you don’t run into resource contention.

71. 在源和目標端都有一張表‘TEST’，但表結構不同，我該如何複製這張表的資料?

OGG by default assumes that the sources and target tables are identical. A table is said to be identical if and only if the table structure, data type and column order are the same on both the source and the target.

If the tables are not identical you must use the parameter ‘SOURCEDEFS’ pointing to the source table definition and ‘COLMAP’ parameter to map the columns from source to target.

72. OGG中刪除佇列檔案的最佳方式是怎樣的?

Use the manager process to delete the extract files after they are consumed by the extract/replicat process

PURGEOLDEXTRACTS /u01/app/oracle/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 2

73. 有什麼引數可以使OGG只解析歸檔資料，而不是線上日誌?

Use the TRANLOGOPTIONS ARCHIVEDLOGONLY option in the parameter file.

74. 現在有一條單向複製鏈路，運維人員想針對源和目標端伺服器安裝作業系統補丁，請說明一下在打補丁前後應該執行的操作步驟.

Procedure:

Check to make sure that the Extract has processed all the records in the data source (Online Redo/archive logs)
GGSCI> send extract , logend
(The above command should print YES)

Verify the extract, pump and replicat has zero lag.
GGSCI> send extract , getlag
GGSCI> send extract , getlag
GGSCI> send replicat , getlag
(The above command should pring “At EOF, no more records to process.”)

Stop all application and database activity.
Make sure that the primary extract is reading the end of the redolog and that there is no LAG at all for the processes.
Now proceed with stopping the processes:

Source:

Stop the primary extract
Stop the pump extract
Stop the manager process
Make sure all the processes are down.

Target:

Stop replicat process
Stop mgr
Make sure that all the processes are down.
Proceed with the maintenance
After the maintenance, proceed with starting up the processes: