GoldenGate Extract Archived Log Only (ALO) Mode Template Best Practices (文档 ID 1482439.1) 转到底部
________________________________________
In this Document
Purpose
Scope
Details
Description
How It Works
Restrictions
Requirements
Configuration Options
Parameter File Options for TRANLOGOPTIONS
Known Issues
References
________________________________________
APPLIES TO:
Oracle GoldenGate
Information in this document applies to any platform.
PURPOSE
This article provides a technical overview and template sample of using Archived-Log-Only (ALO) mode of Oracle GoldenGate.
SCOPE
The article is intended for DBAs and GoldenGate personnel who need to understand the basic concepts, usability, and initial setup for Oracle GoldenGate extracting from Oracle archived log files only, as known as ALO (archived-log-only) mode.
It requires a basic understanding and skills of Oracle GoldenGate and Oracle Database.
DETAILS
Description
The idea behind ALO is to leverage Oracle GoldenGate Extract functionality making it independent of the target database (replicat) and with minimum or no distress to the source database. This feature allows users to extract data from Oracle archived log files directly without frequently accessing the source database. OGG makes an initial connection for object look up depending on the configuration mode user (see details below).
There are several uses for ALO mode, especially minimizing the source database overhead, and Oracle Data Guard switchover/fail-over strategies.
This Oracle GoldenGate functionality applies for both Oracle RAC instances and Oracle standalone databases.
How It Works
Although there are a couple of parameters that can be added to the Extract parameter file, it is only one parameter that tells Extract to act in ALO mode, the ARCHIVEDLOGONLY option of Extract TRANLOGOPTIONSparameter.
In ALO mode, Oracle GoldenGate Extract will write changes to the trail files for the database objects in interest as soon as the database archived log file is made available accordingly to Extract parameters setup. Oracle GoldenGate connects to the secondary database to get metadata and other required data as needed. As an alternative, ALO mode is supported on the production system as well.
There are different options available that makes ALO flexible within a certain range of database configurations. Some restrictions apply according to its configuration as detailed below.
Restrictions
i. Archived log only mode is not compatible with an Extract operating in integrated capture mode.
ii. Archived log only mode does not support archived log files in ASM (Oracle Automatic Storage Management) mode for versions prior OGG 12c. The archived log files must be outside the ASM environment for Extract to read them.
iii. If using within Oracle Standby (DataGuard) configuration:
a. RESETLOGS can not be done at source database.
b. ALO can not be used if the primary system is Oracle RAC and the standby database is non-RAC.
iv. Oracle GoldenGate versions prior 10.4 do not support ALO for Oracle RAC databases.
Requirements
i. If using in a Oracle Standby (DataGuard) configration where the primary is a RAC database, the standby should be a RAC system as well.
ii. If using with Oracle RAC:
a. It requires a dedicated connection to the source server. If that connection is lost, Oracle GoldenGate processing will stop.
b. The directories that contain the archive logs must have unique names across all nodes; otherwise, Extract may return "out of order SCN" errors.
c. If one or more RAC instances may be idle, you may need to perform archive log switching on the idle nodes to ensure that operations from the active instances are recorded in the trail file in a timely manner.
You can instruct the Oracle RDBMS to do this log archiving automatically at a preset interval by setting the archive_lag_target parameter.
For example, to ensure that logs are archived every fifteen minutes, regardless of activity, you can issue the following command in all instances of the RAC system:
SQL> alter system set archive_lag_target 900;
iii. The Extract LOGRETENTION parameter defaults to DISABLED when Extract is in archived log only mode. For the classical Extract it comes ENABLED by default.
Configuration Options
I. Setting up a local extract in ALO mode to read the archived log files of a local Oracle Database named ORCL.
1. Enable supplemental logging at the table level and the database level for the tables in the source database. Log in to SQL*Plus as a user with ALTER SYSTEM privilege, and then issue the following command to determine whether the source database is in forced logging mode:
$ sqlplus / as sysdba
SELECT force_logging FROM v$database;
-- Enable forced logging.
ALTER DATABASE FORCE LOGGING;
-- Verify that forced logging is enabled.
SELECT force_logging FROM v$database;
-- Switch the log files.
ALTER SYSTEM SWITCH LOGFILE;
2. Create the Database username and give the appropriate privileges to manage the GoldenGate tasks, in this sample, "source":
$ sqlplus / as sysdba
grant resource, dba, connect to source identified by source;
exec dbms_streams_auth.grant_admin_privilege('source')
grant become user to source;
3. When Oracle GoldenGate is running on a different server from the source database, make certain that SQL*Net is configured properly to connect to a remote server, such as providing the correct entries in a TNSNAMES.ora file.
Extract must have permission to maintain a SQL*Net connection to the source database. This is an example of an TNSNAMES.ora alias / connect string:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <source_server_ip> )(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
4. Use a SQL*Net connect string in:
4.1. The USERID parameter in the parameter file of every Oracle GoldenGate process that connects to that database.
$ ggsci
DBLOGIN USERID source, PASSWORD source
Successfully logged into database.
4.2. The DBLOGIN command in GGSCI.
Example USERID statement:
$ ggsci
DBLOGIN USERID source@orcl, PASSWORD source
Successfully logged into database.
NOTE: If you have a standby server that is local to the server that Oracle GoldenGate is running on, you do not need to use a connect string in USERID. You can just supply the user login name.
5. Add the transaction for table DEPT of sample schema SCOTT for testing purpose :
add trandata scott.dept
-- check the trandata was added successfully
info trandata scott.DEPT
Columns supplementally logged for table SCOTT.DEPT: DEPTNO.
Note: Use database script $ORACLE_HOME/rdbms/admin/utlsampl.sql to create SCOTT schema if it does not exit.
6. Use the Extract parameter TRANLOGOPTIONS with the ARCHIVEDLOGONLY option. This option forces Extract to operate in ALO mode against a primary or logical standby database, as determined by a value of PRIMARY or LOGICAL STANDBY in the DATABASE_ROLE column of the v$database view.
$ sqlplus / as sysdba
select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
Note: The default is to read the online logs. TRANLOGOPTIONS with ARCHIVEDLOGONLY is not needed if using ALO mode against a physical standby database, as determined by a value of PHYSICAL STANDBY in the DATABASE_ROLE column of v$database. Extract automatically operates in ALO mode if it detects that the database is a physical standby.
$ ggsci
edit param EXT_ALO
Insert following parameter file for the purpose of testing with table DEPT from Oracle RDBMS sample schema SCOTT for the extract that will be named as "EXT_ALO":
extract EXT_ALO
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID source@orcl, PASSWORD source
discardfile ./dirrpt/EXT_ALO.dsc, purge, megabytes 200
statoptions reportfetch
exTTRAIL ./dirdat/rt
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
TRANLOGOPTIONS ALTARCHIVELOGDEST primary /u4/app/oracle/oradata/ORCL/arch
table SCOTT.DEPT;
Note: Make the appropriated adjustments for TRANLOGOPTIONS ALTARCHIVELOGDEST and USERID as per your environment settings.
Add the Extract group by issuing the ADD EXTRACT command with a timestamp as the BEGIN option, or by using ADD EXTRACT with the SEQNO and RBA options. It is best to give Extract a known start point at which to begin extracting data, rather than by using the NOW argument. The start time of “NOW” corresponds to the time of the current online redo log, but an ALO Extract cannot read the online logs, so it must wait for that log to be archived when Oracle switches logs.
The timing of the switch depends on the size of the redo logs and the volume of database activity, so there might be a lag between when you start Extract and when data starts being captured. This can happen in both regular and RAC database configurations.
ADD EXTRACT EXT_ALO, TRANLOG, BEGIN NOW,THREADS 1
ADD EXTTRAIL ./dirdat/rt, EXTRACT EXT_ALO, MEGABYTES 5
-- start extract
START EXTRACT EXT_ALO
-- check extrac info:
INFO EXTRACT EXT_ALO
EXTRACT EXT_ALO Last Started 2012-10-02 22:15 Status RUNNING
Checkpoint Lag 00:00:00 (updated 593:44:44 ago)
Log Read Checkpoint Oracle Redo Logs
2012-10-03 22:01:32 Thread 1, Seqno 6617, RBA 13192
SCN 2344.1618255213 (10069021597037)
-- If the extract Status shows "ABENDED", you can start troubleshooting via extract report. This will produce a report with the failure point. You may refer to the
-- Oracle GoldenGate main note for further assistance in troubleshooting the processes, Doc ID:1306476.1.
VIEW REPORT EXT_ALO
7. Other TRANLOGOPTIONS options might be required for your environment. For example, depending on the copy program that you use, you might need to use the COMPLETEARCHIVEDLOGONLY option to prevent Extract errors. See next section for more details.
8. You can now issue any DML for the table in interest and check the trail fail accordingly, e.g.:
$ sqlplus / as sysdba
insert into scott.dept values (99, 'SUPPORT' , 'ORLANDO' );
commit;
-- switch the redo log to force current redo to be archived:
alter system switchlogfile;
Use GoldenGate Logdump tool to visualize the trail files and see the recently inserted row for SCOTT.DEPT table:
$ cd goldendate>
$ ls -ltr dirdat/
-rw-rw-rw- 1 oracle oinstall 1047 Oct 2 22:15 rt000000
-rw-rw-rw- 1 oracle oinstall 1109 Oct 30 12:09 rt000001
-rw-rw-rw- 1 oracle oinstall 1109 Oct 30 17:15 rt000002
-rw-rw-rw- 1 oracle oinstall 1740 Oct 30 17:42 rt000003
$ logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
open dirdat/rt000003
Current LogTrail is /u1/app/ogg/dirdat/rt000003
filter include filename SCOTT.*
filter match all
n
2012/10/30 17:42:36.000.000 Insert Len 44 RBA 1576
Name: SCOTT.DEPT
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 0063 0001 000b 0000 | .............c......
0007 5355 5050 4f52 5400 0200 0b00 0000 074f 524c | ..SUPPORT........ORL
414e 444f | ANDO
exit
II. Setting up of ALO for Oracle Data Guard switchover and failover operations.
Extact parameter file (after failover --ALO mode with hardcoded reseetlogs_id):
**********************************************************************
extract edg
userid sb, password sb
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST /u01/app/oracle/archivelog/
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_747322002.dbf
FETCHOPTIONS, NOUSESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
exttrail ./dirdat/et
table schema_name.<table_name>;
**********************************************************************
Note: Please refer to the Doc ID 1323670.1 for the complete reference and steps to setup such configuration.
Parameter File Options for TRANLOGOPTIONS
ARCHIVEDLOGONLY ARCHIVEDLOGONLY causes Extract to read from the archived logs exclusively, without querying or validating the logs from system views such as v$log and v$archived_log. This parameter puts Extract into Archived Log Only mode (ALO). By default, Extract does not use archived log-only mode even if the database that it connects to is a physical standby database.
COMPLETEARCHIVEDLOGONLY | NOCOMPLETEARCHIVEDLOGONLY Overrides the default Extract processing of archived logs. Possible conditions for this parameter:
i. Default in regular mode: NOCOMPLETEARCHIVEDLOGONLY.
Extract starts processing redo data from an archived log immediately when it becomes available, without waiting for it to be written completely to disk.
Override in regular mode: Use COMPLETEARCHIVEDLOGONLY to force Extract to wait until an archived log is written completely to disk before starting to process redo data.
ii. Default in archived log only (ALO) mode: COMPLETEARCHIVEDLOGONLY.
Forces Extract to wait for the archived log to be written to disk completely before starting to process redo data.
Override in ALO mode: Use NOCOMMPLETEARCHIVEDLOGONLY to force Extract to start processing redo data from an archived log immediately when it becomes available.
This parameter applies when copying production (source) archive logs to a secondary database where they will serve as the data source. Some Oracle programs do not build the archive log from the first byte to the last byte in sequential order, but instead may copy the first 500MB, then the last 500MB, and finally the middle 1000MB, for example.
If Extract begins reading at the first byte, it will abend when it reaches the break in the byte sequencing. Waiting for the whole file to be written prevents this problem.
Note that Extract starts to read an archive file before it is completely written to disk, but whether or not it starts to capture data before the file is complete depends on the conditions stated previously.
COMPLETEARCHIVEDLOGTIMEOUT <seconds> Controls the number of seconds that Extract waits, when in COMPLETEARCHIVEDLOGONLY mode, to try again if it cannot validate that a redo log is being completely written to disk.
Use this option in conjunction with the COMPLETEARCHIVEDLOGONLY option of TRANLOGOPTIONS. This option is disabled by default, and Extract will abend after ten seconds if it cannot validate that the file is being written to disk. This check is performed by reading the block header from the last block and verifying against the expected sequence number to determine if the last block has been written out. For use any value greater than 0.
ALTARCHIVEDLOGFORMAT <string>
[INSTANCE <instance_name>]
[THREADID <id>] Specifies a string that overrides the archive log format of the source database. <string> accepts the same specifier as Oracle's parameter LOG_ARCHIVE_FORMAT. Extract uses the supplied format specifier to derive the log file name.
Example: arch_%T.arc
Note: For complete details please access the Oracle GoldenGate online documentation for your current version here
ALTARCHIVELOGDEST
[PRIMARY]
[INSTANCE <instance_name>]
[THREADID <id>]
<path name> Points Extract to the archived or backup Oracle transaction logs when they reside somewhere other than the default location. Extract first checks the specified location and then checks the default location.
<path name> specifies the fully qualified path to the archived logs in the alternate directory. This directory must be NFS mounted to the node where Oracle GoldenGate is running. Use that mount point for ALTARCHIVELOGDEST.
Note: For complete details please access the Oracle GoldenGate online documentation for your current version here
Known Issues
Bug 11827736: ALO-- EXTRACT DOESN'T RECOGNIZE %D OF ARCHIVE OG FILE FORMAT. Fixed in Product Version 12.1.1.0.0.
Doc ID 972329.1 Why Do I Get The Error Message "GGS Error 118 : Unknown Specifier In Archive Log Format"?
REFERENCES
NOTE:1358342.1 - Oracle GoldenGate - Best Practice - Extracting from Oracle Archive log files (ALO Mode)
NOTE:1433357.1 - FAQ for Oracle GoldenGate Extract in ALO mode on Oracle Database.
NOTE:1357786.1 - OGG Replicating Oracle source data to multiple Oracle targets using ALO
NOTE:972329.1 - Why Do I Get The Error Message "GGS Error 118 : Unknown Specifier In Archive Log Format"?
NOTE:1306476.1 - Main Note - Oracle GoldenGate - Troubleshooting
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖