3.1 Tratado de Libre Comercio Chile-Estados Unidos
3.1.1 Breves Antecedentes de la Negociación
In this practice, you set up the change capture on the source database, together with its associated ExtTrail, and the change delivery on the target side. The Replicat parameters will force the Replicat process to back up the tablespace that is hosting the database objects that belong to the trg_user user when the PERFORM BACKUP string is stored in a predefined table.
Assumptions
The Oracle database CDB12c is running in ARCHIVELOG mode; the src_user and trg_user users are created in the database and have the necessary privileges for replication. The default tablespace for src_user and trg_user is OGGDATA.
Tasks
1. Rearrange your desktop environment in such a way that you have four windows aligned with each other. The upper left window has the caption "OGG_SRC" and in it, GGSCI is connected to the replication source (/u03/ogg/ogg_src). The window to its right has the caption "OGG_TRG" and in it, GGSCI is connected to the replication target
(/u03/ogg/ogg_trg). Below these windows, you have on the left a window with the caption "SRC_USER," which hosts a sqlplus session connected to the src_user schema in the ogg1 PDB database. On the right, your environment displays a window with the caption "TRG_USER," which hosts a sqlplus session connected to the trg_user schema in the ogg2 PDB database:
Oracle University and Error : You are not a Valid Partner use only
2. Make sure that the two sqlplus sessions, SRC_USER and TRG_USER, have been started while the default directory is ~/labs/Practice05/configs for both environments. This practice will instruct you to run scripts that are stored in this directory, so the SQL scripts that start with the at (@) sign assume that the current directory is
~/labs/Practice05/configs. If you are not sure that the sqlplus sessions have been started from the current directory, exit sqlplus, navigate to the
~/labs/Practice05/configs directory, and relaunch sqlplus.
3. Create a table called EVENT_TRIGGER in both the source and target environments. Click the SRC_USER window and create the table in the replication source environment:
SQL> create table event_trigger (id number not null primary key, trigger_type varchar2(255));
Table created.
SQL>
4. Click the TRG_USER window and create the same table in the replication target environment:
SQL> create table event_trigger (id number not null primary key, trigger_type varchar2(255));
Table created.
SQL>
5. Click the OGG_SRC window and use the DBLogin command to connect to the CDB12c database by using the ogg_admin alias; verify that the Oracle GoldenGate Manager is running by issuing the INFO MGR command:
GGSCI ([HOST]) > DBLogin UserIdAlias ogg_admin Successfully logged into database CDB$ROOT.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info mgr Manager is running (IP port SRCHOST.7809, Process ID 1576).
6. Verify that the table that you just created has supplemental logging enabled in the
src_user schema. The src_user schema in the ogg1 PDB should automatically enable supplemental logging because you issued the command "ADD SCHEMATRANDATA
src_user ALLCOLS" in Practice 2-2, step 5.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info trandata ogg1.src_user.*
...many lines omitted for clarity...
Logging of supplemental redo log data is enabled for table OGG1.SRC_USER.EVENT_TRIGGER.
Columns supplementally logged for table OGG1.SRC_USER.EVENT_TRIGGER:
ID, TRIGGER_TYPE.
...many lines omitted for clarity...
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) >
Oracle University and Error : You are not a Valid Partner use only
7. Edit the EUEVT primary Extract, which captures all table activity in the src_user schema (ogg1 PDB):
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > edit params euevt
Extract euevt
Exttrail ./dirdat/eu UseridAlias ogg_admin LOGALLSUPCOLS
tranlogoptions integratedparams (max_sga_size 128) UPDATERECORDFORMAT COMPACT
SOURCECATALOG ogg1 Table src_user.*;
8. Exit from the editor, saving the parameter file. In GGSCI, register the EUEVT Extract with the database (ogg1) and add the newly created Extract:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > REGISTER EXTRACT euevt DATABASE CONTAINER (ogg1)
Extract EUEVT successfully registered with database at SCN 2709383.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add extract euevt, integrated Tranlog, begin now
EXTRACT added.
9. Connect the Extract to the ExtTrail, setting the exttrail file size to 10 MB:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add exttrail ./dirdat/eu, Extract euevt, megabytes 10
EXTTRAIL added.
10. Add the puevt Data Pump Extract, which physically transfers the captured data to the target host:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > edit params puevt
Extract puevt
SETENV (ORACLE_SID='CDB12c')
rmthost ogg_target, mgrport 7909, Compress rmttrail ./dirdat/pu
passthru
SOURCECATALOG ogg1 table src_user.*;
11. Connect the Data Pump to the local exttrail file:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add extract puevt, exttrailsource ./dirdat/eu
EXTRACT added.
Oracle University and Error : You are not a Valid Partner use only
12. Connect the remote trail file to the Data Pump, setting the remote trail file size to 10 MB:
GGSCI ([SRCHOST]) 9> add rmttrail ./dirdat/pu, Extract puevt, megabytes 10
RMTTRAIL added.
13. Start the Extract and Data Pump processes, check their status, and exit from GGSCI:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > start extract euevt
Sending START request to MANAGER ...
EXTRACT EUEVT starting
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > start extract puevt
Sending START request to MANAGER ...
EXTRACT PUEVT starting
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info all Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EUEVT 00:00:00 00:41:39 EXTRACT RUNNING PUEVT 00:00:00 00:00:06
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > exit
14. Navigate to the /home/oracle directory and use an editor of your choice to create the backup.cmd file, which contains the following RMAN syntax:
connect target sys/<password>@ogg2 run {
allocate channel t1 device type disk format '/home/oracle/rman_backup/%U';
backup tablespace oggdata;
backup archivelog all;
}
15. In the same directory, create the rman_backup directory and the shell file that invokes RMAN, passing the parameters contained in the backup.cmd file to it.
[OS prompt] $ mkdir rman_backup [OS prompt] $
16. The shell file name is rman_bck.sh. Use an editor of your choice to create rman_bck.sh and add the lines below:
#!/bin/bash
rman @/home/oracle/backup.cmd
17. Make the script executable by using the Linux chmod command:
[OS prompt]$ chmod +x rman_bck.sh
Oracle University and Error : You are not a Valid Partner use only
18. Select the OGG_TRG window and use the DBLogin command to connect to CDB12c by using the ogg_repl alias; verify that the Oracle GoldenGate Manager is running:
GGSCI ([HOST]) > DBLogin UserIdAlias ogg_repl Successfully logged into database OGG2.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) 12> info mgr
Manager is running (IP port edddr60p1.7909, Process ID 1571).
19. Create the Replicat parameter file for the Replicat REVTA, specifying the filter clause that will cause the RMAN backup when the "PERFORM BACKUP" string is inserted into the EVENT_TRIGGER table:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > edit params revta Replicat revta
FILTER(ON INSERT, @streq(TRIGGER_TYPE,'PERFORM BACKUP')), EVENTACTIONS (SHELL '/home/oracle/rman_bck.sh', REPORT);
map ogg1.src_user.gdp_by_year, target ogg2.trg_user.gdp_by_year;
map ogg1.src_user.gdp_growth_by_year, target ogg2.trg_user.gdp_growth_by_year;
map ogg1.src_user.economic_entity, target ogg2.trg_user.economic_entity;
20. Save the parameter file. Add the Replicat definition, linking the Integrated Replicat process to its trail file:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > add replicat revta, Integrated exttrail ./dirdat/pu
REPLICAT (Integrated) added.
21. Start the Replicat process:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > start replicat revta
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > info all
Program Status Group Lag at Chkpt Time Since oggChkpt
MANAGER RUNNING
REPLICAT RUNNING REVTA 00:00:00 00:00:06 22. Select the SRC_USER window. Exit sqlplus and navigate to the
~/labs/Practice05/configs directory. Launch sqlplus again, connecting to Oracle as src_user@ogg1. Generate some activity by running the gdp_by_year_2010.sql script:
Oracle University and Error : You are not a Valid Partner use only
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[OS prompt]> cd ~/labs/Practice05/configs [OS prompt]> sqlplus src_user@ogg1
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 15 17:39:30 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password: *****
Last Successful login time: Sun Mar 15 2015 17:38:37 +11:00 Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @gdp_by_year_2010.sql 1 row created.
...many lines omitted for clarity...
23. Now you insert the row that will trigger the RMAN backup performed by a remote shell that is invoked by the Replicat process:
SQL> insert into event_trigger values (1,'PERFORM BACKUP');
SQL> commit;
24. Select the OGG_TRG window, where GGSCI is connected to the replication target environment. Enter a view report command on the REVTA Replicat to check that the triggering record has been intercepted, and that the shell action has been performed.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > view report revta 25. You should see lines similar to the ones below, which show that the event was fired by the
Replicat process:
2015-03-15 17:42:20 INFO OGG-06505 MAP resolved (entry ogg1.src_user.event_trig
ger): map "OGG1"."SRC_USER"."EVENT_TRIGGER", TARGET ogg2.trg_user.event_trigger, FILTER(ON INSERT,
@streq(TRIGGER_TYPE,'PERFORM BACKUP')), EVENTACTIONS (SHELL '/home/oracle/rman_bck.sh', REPORT).
2015-03-15 17:42:21 INFO OGG-01286 Executing shell command '/home/oracle/rman_bck.sh' due to SHELL event for target table OGG2.TRG_USER.EVENT_TRIGGER in file ./dirdat/pu000000, RBA 34527.
26. The instructions contained in the shell command file are all listed in the report produced by Replicat for the REVTA Replicat, together with the output generated by the shell.
Oracle University and Error : You are not a Valid Partner use only
27. Open an additional shell window and, at the OS prompt, check that the RMAN backup files exist in the directory specified by the RMAN script. Then type exit to close the shell window:
[OS prompt]> ls -lh rman_backup/
total 3.3M
-rw-r---. 1 oracle oinstall 3.3M Mar 15 17:42 07q1th7u_1_1 [OS prompt]> exit
The names of the RMAN backup files will vary in your environment; the important thing is to make sure that RMAN backup files have been created.
28. Select the OGG_TRG window. In the GGSCI session connected to the OGG_TRG Oracle GoldenGate instance, stop and delete the REVTA Replicat. Erase the RMTTRAIL file associated with the Replicat (./dirdat/pu*) by using a SHELL command (sh rm −f):
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > stop replicat revta Sending STOP request to REPLICAT REVTA ...
Request processed.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > delete replicat revta
2015-03-15 17:59:34 INFO OGG-02529 Successfully unregistered REPLICAT REVTA inbound server OGG$REVTA from database.
Deleted REPLICAT REVTA.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) >
29. Select the OGG_SRC window and launch GGSCI. Connect to the database as ogg_admin and stop and delete the EUEVT and PUEVT Extract groups. Then unregister the EUEVT Extract from the CDB12c database:
[OS propmt] $ oggsrc [OS propmt] $ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34 Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI ([HOST]) DBLogin UserIdAlias ogg_admin Successfully logged into database CDB$ROOT.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info all Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EUEVT 00:00:00 00:00:03 EXTRACT RUNNING PUEVT 00:00:00 00:00:01
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > stop er * Sending STOP request to EXTRACT EUEVT ...
Oracle University and Error : You are not a Valid Partner use only
Request processed.
Sending STOP request to EXTRACT PUEVT ...
Request processed.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > delete er * ! Deleted EXTRACT EUEVT.
Deleted EXTRACT PUEVT.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > UNREGISTER EXTRACT euevt DATABASE
Successfully unregistered EXTRACT EUEVT from database GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) >. This completes Practice 5-1. Continue with Practice 5-2.