ogg测试环境本机快速搭建：查看insert into select 和create table as _sqlora

http://blog.sina.com.cn/u/5592732073

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

ogg测试环境本机快速搭建：查看insert into select 和create table as

(2015-11-26 14:05:53)

分类： ogg

测试目的：查看insert into select 和create table as select这两种动作在OGG中的表现。

准备ogg用户及测试脚本：

Microsoft Windows [版本 6.1.7601]

C:\Users\Administrator>set ORACLE_SID=JYC

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 11月 26 12:49:55 2015

SQL> conn /as sysdba

已连接。

SQL> select name from v$datafile;

NAME

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

D:\ORACLE\ORADATA\JYC\SYSTEM01.DBF

D:\ORACLE\ORADATA\JYC\SYSAUX01.DBF

D:\ORACLE\ORADATA\JYC\UNDOTBS01.DBF

D:\ORACLE\ORADATA\JYC\USERS01.DBF

D:\ORACLE\ORADATA\JYC\TEST.DBF

D:\ORACLE\ORADATA\JYC\T2.DBF

D:\ORACLE\ORADATA\JYC\T3.DBF

已选择7行。

SQL> conn jyc/jyc

已连接。

SQL> select name from v$tempfile;

NAME

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

D:\ORACLE\ORADATA\JYC\TEMP01.DBF

SQL> @d:\oracle\tbs.sql

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)

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

SYSAUX 640 81920 516.44 80.69 123.56

SYSTEM 1024 131072 722 70.51 302

T3 15 1920 9 60 6

T2 15 1920 9 60 6

USERS 2048 262144 815.62 39.83 1232.38

TEST 10 1280 1 10 9

UNDOTBS1 810 103680 58.25 7.19 751.75

TEMP 60 7680 0

已选择8行。

SQL> alter database datafile 1 resize 900M;

数据库已更改。

SQL> alter database datafile 4 resize 1000M;

alter database datafile 4 resize 1000M

第 1 行出现错误:

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE

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

MGMT_VIEW SYSTEM

SYS SYSTEM

SYSTEM SYSTEM

DBSNMP SYSAUX

SYSMAN SYSAUX

LN_LINLN USERS

JYC USERS

TEST USERS

ln-linln USERS

OUTLN SYSTEM

FLOWS_FILES SYSAUX

USERNAME DEFAULT_TABLESPACE

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

MDSYS SYSAUX

ORDSYS SYSAUX

EXFSYS SYSAUX

WMSYS SYSAUX

APPQOSSYS SYSAUX

ORDDATA SYSAUX

CTXSYS SYSAUX

ANONYMOUS SYSAUX

XDB SYSAUX

ORDPLUGINS SYSAUX

SI_INFORMTN_SCHEMA SYSAUX

USERNAME DEFAULT_TABLESPACE

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

OLAPSYS SYSAUX

ORACLE_OCM USERS

XS$NULL USERS

MDDATA USERS

DIP USERS

APEX_PUBLIC_USER USERS

SPATIAL_CSW_ADMIN_USR USERS

SPATIAL_WFS_ADMIN_USR USERS

已选择30行。

SQL> select name from v$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEST

TEMP

已选择8行。

SQL> drop user test cascade;

用户已删除。

SQL> drop tablespace test including contents and datafiles;

表空间已删除。

SQL> archive log list;

ORA-01031: 权限不足

SQL> conn /as sysdba

已连接。

SQL> archive log list;

数据库日志模式非存档模式

自动存档禁用

存档终点 D:\oracle\product\11.2.0\dbhome_1\RDBMS

最早的联机日志序列 310

当前日志序列 312

SQL> show parameter dest;

NAME TYPE VALUE

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

audit_file_dest string D:\ORACLE\ADMIN\JYC\ADUMP

background_dump_dest string D:\oracle\diag\rdbms\jyc\jyc\t

core_dump_dest string D:\oracle\diag\rdbms\jyc\jyc\c

cursor_bind_capture_destination string memory+disk

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

db_recovery_file_dest string

NAME TYPE VALUE

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

db_recovery_file_dest_size big integer 0

diagnostic_dest string D:\ORACLE

log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

log_archive_dest_11 string

log_archive_dest_12 string

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

NAME TYPE VALUE

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

log_archive_dest_17 string

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

NAME TYPE VALUE

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

log_archive_dest_27 string

log_archive_dest_28 string

log_archive_dest_29 string

log_archive_dest_3 string

log_archive_dest_30 string

log_archive_dest_31 string

log_archive_dest_4 string

log_archive_dest_5 string

log_archive_dest_6 string

log_archive_dest_7 string

log_archive_dest_8 string

NAME TYPE VALUE

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

log_archive_dest_9 string

log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_11 string enable

log_archive_dest_state_12 string enable

log_archive_dest_state_13 string enable

log_archive_dest_state_14 string enable

log_archive_dest_state_15 string enable

log_archive_dest_state_16 string enable

log_archive_dest_state_17 string enable

log_archive_dest_state_18 string enable

NAME TYPE VALUE

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

log_archive_dest_state_19 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

NAME TYPE VALUE

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

log_archive_dest_state_29 string enable

log_archive_dest_state_3 string enable

log_archive_dest_state_30 string enable

log_archive_dest_state_31 string enable

log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable

log_archive_dest_state_6 string enable

log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

log_archive_dest_state_9 string enable

log_archive_duplex_dest string

NAME TYPE VALUE

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

log_archive_min_succeed_dest integer 1

standby_archive_dest string %ORACLE_HOME%\RDBMS

user_dump_dest string D:\oracle\diag\rdbms\jyc\jyc\t

SQL> alter system set log_archive_dest='E:\ogg\archive' scope=both;

系统已更改。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 2555445248 bytes

Fixed Size 2257912 bytes

Variable Size 855641096 bytes

Database Buffers 1677721600 bytes

Redo Buffers 19824640 bytes

数据库装载完毕。

SQL> alter database archivelog;

数据库已更改。

SQL> archive log list;

数据库日志模式存档模式

自动存档启用

存档终点 E:\ogg\archive

最早的联机日志序列 310

下一个存档日志序列 312

当前日志序列 312

SQL> alter database open;

数据库已更改。

SQL> show parameter event;

NAME TYPE VALUE

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

event string

xml_db_events string enable

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 2555445248 bytes

Fixed Size 2257912 bytes

Variable Size 855641096 bytes

Database Buffers 1677721600 bytes

Redo Buffers 19824640 bytes

数据库装载完毕。

数据库已经打开。

SQL> alter database force logging;

数据库已更改。

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

SQL> alter database add supplemental log data;

数据库已更改。

SQL> create tablespace ogg datafile 'E:\ogg\oradata\ogg01.dbf' size 300M autoextend on;

表空间已创建。

SQL> create user oggadm identified by oggadm default tablespace ogg temporary tablespace temp;

用户已创建。

SQL> GRANT CONNECT TO oggadm;

授权成功。

SQL> GRANT ALTER ANY TABLE TO oggadm;--用于添加表附加日志

2 GRANT ALTER SESSION TO oggadm;

GRANT ALTER ANY TABLE TO oggadm;--用于添加表附加日志

第 1 行出现错误:

ORA-00911: 无效字符

SQL> GRANT CREATE SESSION TO oggadm;

授权成功。

SQL> GRANT FLASHBACK ANY TABLE TO oggadm;

授权成功。

SQL> GRANT SELECT ANY DICTIONARY TO oggadm;

授权成功。

SQL> GRANT SELECT ANY TABLE TO oggadm;

授权成功。

SQL> GRANT RESOURCE TO oggadm;

授权成功。

SQL> GRANT DBA TO oggadm;--启用ddl

2 ;

GRANT DBA TO oggadm;--启用ddl

第 1 行出现错误:

ORA-00911: 无效字符

SQL> GRANT DBA TO oggadm;

授权成功。

SQL> GRANT ALTER ANY TABLE TO oggadm;

授权成功。

SQL> grant execute on DBMS_STREAMS_ADM_UTL_INVOK to oggadm;

授权成功。

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH

第 1 行出现错误:

ORA-02065: 非法的 ALTER SYSTEM 选项

SQL> exec dbms_goldengate_auth.grant_admin_privilege('oggadm','*',TRUE);

PL/SQL 过程已成功完成。

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH

第 1 行出现错误:

ORA-02065: 非法的 ALTER SYSTEM 选项

SQL> create user admin identified by admin default tablespace users;

用户已创建。

SQL> grant dba to admin;

授权成功。

SQL>

SQL> select * from nls_database_parameters where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER VALUE

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

NLS_LANGUAGE SIMPLIFIED CHINESE

NLS_TERRITORY CHINA

NLS_CHARACTERSET ZHS16GBK

SQL> show user;

USER 为 "SYS"

SQL> @marker_setup.sql

SP2-0310: 无法打开文件 "marker_setup.sql"

SQL> exit

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

C:\Users\Administrator>e:

E:\>cd ogg\soft

E:\ogg\soft>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 11月 26 13:16:26 2015

SQL> conn /as sysdba

已连接。

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:oggadm

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to OGGADM

MARKER TABLE

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

MARKER SEQUENCE

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

Script complete.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:oggadm

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using OGGADM as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to OGGADM

CLEAR_TRACE STATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

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

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

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

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

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

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL IGNORE TABLE

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

DDL IGNORE LOG TABLE

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

DDLAUX PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL HISTORY TABLE

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

DDL HISTORY TABLE(1)

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

DDL DUMP TABLES

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

DDL DUMP COLUMNS

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

DDL DUMP LOG GROUPS

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

DDL DUMP PARTITIONS

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

DDL DUMP PRIMARY KEYS

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

DDL SEQUENCE

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

GGS_TEMP_COLS

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

GGS_TEMP_UK

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

DDL TRIGGER CODE STATUS:

Line/pos Error

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

No errors No errors

DDL TRIGGER INSTALL STATUS

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

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

DDL TRIGGER TRACE LEVEL

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

LOCATION OF DDL TRACE FILE

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

D:\oracle\diag\rdbms\jyc\jyc\trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred

name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:oggadm

已写入 file role_setup_set.txt

PL/SQL 过程已成功完成。

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO oggadm;

授权成功。

SQL> @ddl_enable.sql

触发器已更改

SQL> @?/rdbms/admin/dbmspool.sql

程序包已创建。

授权成功。

SQL> @ddl_pin.sql oggadm

PL/SQL 过程已成功完成。

SQL>

SQL> @d:\oracle\tbs.sql

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)

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

SYSAUX 640 81920 516.44 80.69 123.56

SYSTEM 900 115200 722 80.22 178

T3 15 1920 9 60 6

T2 15 1920 9 60 6

USERS 2048 262144 815.62 39.83 1232.38

UNDOTBS1 810 103680 58.25 7.19 751.75

TEMP 60 7680 3 5 57

OGG 300 38400 2.69 .9 297.31

已选择8行。

SQL>

SQL> conn oggadm/oggadm

已连接。

SQL> select count(*),object_type from user_objects group by object_type;

COUNT(*) OBJECT_TYPE

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

2 SEQUENCE

4 PROCEDURE

3 PACKAGE

1 LOB

2 PACKAGE BODY

14 TABLE

18 INDEX

6 FUNCTION

已选择8行。

SQL> select table_name from user_tables;

TABLE_NAME

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

GGS_MARKER

GGS_DDL_RULES

GGS_DDL_RULES_LOG

GGS_SETUP

GGS_DDL_HIST_ALT

GGS_DDL_HIST

GGS_DDL_COLUMNS

GGS_DDL_LOG_GROUPS

GGS_DDL_PARTITIONS

GGS_DDL_PRIMARY_KEYS

GGS_DDL_OBJECTS

TABLE_NAME

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

GGS_TEMP_COLS

GGS_TEMP_UK

GGS_STICK

已选择14行。

SQL> create user bj identified by bj default tablespace users;

用户已创建。

SQL> grant dba to bj;

授权成功。

SQL> conn /as sysdba

已连接。

SQL> show parameter recycle;

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string on

SQL> alter system set recyclebin=off;

alter system set recyclebin=off

第 1 行出现错误:

ORA-02096: 此选项的指定初始化参数不可修改

SQL> alter system set recyclebin=off scope=spfile;

系统已更改。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 2555445248 bytes

Fixed Size 2257912 bytes

Variable Size 855641096 bytes

Database Buffers 1677721600 bytes

Redo Buffers 19824640 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter name;

NAME TYPE VALUE

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

db_file_name_convert string

db_name string jyc

db_unique_name string jyc

global_names boolean FALSE

instance_name string jyc

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string jyc

SQL>

SQL> conn admin/admin

已连接。

SQL> exit

==准备ogg软件及配置：

Microsoft Windows [版本 6.1.7601]

C:\Users\Administrator>e:

E:\>cd ogg/soft

E:\ogg\soft>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.28 19820451 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_150116.0606

Windows x64 (optimized), Oracle 11g on Jan 16 2015 07:38:00

GGSCI (zxm) 1> create subdirs

Creating subdirectories under current directory E:\ogg\soft

Parameter files E:\ogg\soft\dirprm: already exists

Report files E:\ogg\soft\dirrpt: created

Checkpoint files E:\ogg\soft\dirchk: created

Process status files E:\ogg\soft\dirpcs: created

SQL script files E:\ogg\soft\dirsql: created

Database definitions files E:\ogg\soft\dirdef: created

Extract data files E:\ogg\soft\dirdat: created

Temporary files E:\ogg\soft\dirtmp: created

Stdout files E:\ogg\soft\dirout: created

GGSCI (zxm) 2> edit params ./GLOBALS

GGSCI (zxm) 3> view param ./GLOBALS

GGSCHEMA oggadm

GGSCI (zxm) 4> edit mgr

ERROR: Invalid command.

GGSCI (zxm) 5> edit param mgr

GGSCI (zxm) 6> dblogin userid oggadm, password oggadm

Successfully logged into database.

GGSCI (zxm) 7> add schematrandata admin

2015-11-26 13:22:45 INFO OGG-01788 SCHEMATRANDATA has been added on schema admin.

GGSCI (zxm) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (zxm) 9> dblogin userid oggadm, password oggadm

Successfully logged into database.

GGSCI (zxm) 10> add extract esource tranlog, threads 1, begin now

EXTRACT added.

GGSCI (zxm) 11> add exttrail ./dirdat/es, extract esource, megabytes 100

EXTTRAIL added.

GGSCI (zxm) 12> edit param esource

GGSCI (zxm) 13> view param esource

EXTRACT ESOURCE

TRANLOGOPTIONS RAWDEVICEOFFSET 0

TRANLOGOPTIONS CONVERTUCS2CLOBS

EXTTRAIL ./dirdat/es

SETENV (ORACLE_SID = "jyc")

SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")

USERID oggadm, PASSWORD AACAAAAAAAAAAAGACBRBDBRGUJAGXCKH, ENCRYPTKEY DEFAULT

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

NOCOMPRESSDELETES

gettruncates

GETUPDATEBEFORES

DDL INCLUDE MAPPED EXCLUDE OBJTYPE 'TRIGGER'

DDLOPTIONS ADDTRANDATA RETRYOP MAXRETRIES 10 RETRYDELAY 10

TABLE ADMIN.*;

GGSCI (zxm) 14> edit param esource

GGSCI (zxm) 15> add extract psource, exttrailsource ./dirdat/es

EXTRACT added.

GGSCI (zxm) 16> add rmttrail E:\ogg\soft\dirdat\ps, extract psource, megabytes 100

RMTTRAIL added.

GGSCI (zxm) 17> edit param psource

GGSCI (zxm) 18> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT STOPPED ESOURCE 00:00:00 00:06:12

EXTRACT STOPPED PSOURCE 00:00:00 00:01:47

GGSCI (zxm) 19> ADD CHECKPOINTTABLE GGS_CHECKPOINT

Successfully created checkpoint table GGS_CHECKPOINT.

GGSCI (zxm) 20> edit params ./GLOBALS

GGSCI (zxm) 21> add replicat rsource, exttrail E:\ogg\soft\dirdat\ps, checkpointtable ggs_checkpoint

REPLICAT added.

GGSCI (zxm) 22> edit param rsource

GGSCI (zxm) 23> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT STOPPED ESOURCE 00:00:00 00:10:12

EXTRACT STOPPED PSOURCE 00:00:00 00:05:47

REPLICAT STOPPED RSOURCE 00:00:00 00:00:29

GGSCI (zxm) 24> start mgr

Manager started.

GGSCI (zxm) 25> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:10:28 00:00:05

EXTRACT RUNNING PSOURCE 00:00:00 00:00:02

REPLICAT STOPPED RSOURCE 00:00:00 00:00:50

GGSCI (zxm) 26> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:10:28 00:00:07

EXTRACT RUNNING PSOURCE 00:00:00 00:00:04

REPLICAT STOPPED RSOURCE 00:00:00 00:00:52

GGSCI (zxm) 27> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:00:00 00:00:00

EXTRACT RUNNING PSOURCE 00:00:00 00:00:07

REPLICAT STOPPED RSOURCE 00:00:00 00:00:55

GGSCI (zxm) 28> start rsource

Sending START request to MANAGER ...

REPLICAT RSOURCE starting

GGSCI (zxm) 29> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:00:00 00:00:02

EXTRACT RUNNING PSOURCE 00:00:00 00:00:00

REPLICAT RUNNING RSOURCE 00:00:00 00:00:05

GGSCI (zxm) 30> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:00:00 00:00:10

EXTRACT RUNNING PSOURCE 00:00:00 00:00:07

REPLICAT RUNNING RSOURCE 00:00:00 00:00:09

GGSCI (zxm) 31> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING ESOURCE 00:00:00 00:00:01

EXTRACT RUNNING PSOURCE 00:00:00 00:00:07

REPLICAT RUNNING RSOURCE 00:00:00 00:00:09

GGSCI (zxm) 32> stop rsource

Sending STOP request to REPLICAT RSOURCE ...

Request processed.

GGSCI (zxm) 33> stop psource

Sending STOP request to EXTRACT PSOURCE ...

Request processed.

GGSCI (zxm) 34> stop esource

Sending STOP request to EXTRACT ESOURCE ...

Request processed.

GGSCI (zxm) 35> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

GGSCI (zxm) 36> view param psource

EXTRACT PSOURCE

TRANLOGOPTIONS RAWDEVICEOFFSET 0

TRANLOGOPTIONS CONVERTUCS2CLOBS

USERID oggadm, PASSWORD AACAAAAAAAAAAAGACBRBDBRGUJAGXCKH, ENCRYPTKEY DEFAULT

RMTHOST 127.0.0.1, MGRPORT 7809, COMPRESS

RMTTRAIL E:\ogg\soft\dirdat\ps

PASSTHRU

TABLE ADMIN.*;

GGSCI (zxm) 37> view param rsource

REPLICAT RSOURCE

SETENV (ORACLE_SID = "jyc")

SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")

USERID oggadm, PASSWORD AACAAAAAAAAAAAGACBRBDBRGUJAGXCKH, ENCRYPTKEY DEFAULT

ASSUMETARGETDEFS

ALLOWNOOPUPDATES

discardfile ./dirrpt/rs.dsc, purge, megabytes 500

reperror default, discard

discardrollover

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

gettruncates

DDL INCLUDE ALL

--DDL

DDLERROR 4080 IGNORE

DDLERROR 1435 IGNORE INCLUDE OPTYPE ALTER OBJTYPE SESSION

DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 2 RETRYDELAY 5

--MAPEXCLUDE admin.CE_NET_CABLE

--map ADMIN.CM_LINK, target SMODEL_BJJ.CM_LINK, filter(@getenv('transaction','csn')>14115051667610);

--map ADMIN.CE_DEVICE_CONNECT_BJ, target SMODEL_BJJ.CE_DEVICE_CONNECT_BJ, filter(@getenv('transaction','csn')>14115046415828);

--map ADMIN.CE_NET_CABLE, target SMODEL_BJJ.CE_NET_CABLE, filter(@getenv('transaction','csn')>14115046332927);

--MAPEXCLUDE admin.CE_NET_CABLE

--MAPEXCLUDE ADMIN.CE_DEVICE_CONNECT_BJ

--map ADMIN.CE_NET_CABLE, target SMODEL_BJJ.CE_NET_CABLE, filter(@getenv('transaction','csn')>14115046505816);

--MAP admin.CE_NET_CABLE, TARGET smodel_bjj.CE_NET_CABLE , colmap (usedefaults), REPERROR (-01163, IGNORE), FILTER ( @STRLEN(OPTICAL

_CABLE_MODEL_ID) < 8, ON INSERT, ON DELETE, ON UPDATE, RAISEERROR 21002) ,REPERROR (21002, DISCARD),REPERROR (-01163, DISCARD);

--MAP ADMIN.CE_NET_CABLE, TARGET SMODEL_BJJ.CE_NET_CABLE,colmap (USEDEFAULTS, OPTICAL_CABLE_MODEL_ID = OPTICAL_CABLE_MODEL_ID);

--MAPEXCLUDE admin.CE_NET_CABLE

--MAPEXCLUDE admin.CE_NET_CABLE_1

--MAP ADMIN.CM_LINK, target SMODEL_BJJ.CM_LINK, filter(@getenv('transaction','csn')>14115052136461);

--MAPEXCLUDE admin.CM_LINK

--MAP ADMIN.CE_NET_CABLE, target SMODEL_BJJ.CE_NET_CABLE, filter(@getenv('transaction','csn')>14115053424164);

--MAPEXCLUDE admin.CE_NET_CABLE

--MAP ADMIN.CR_DEVICE_WARE, target SMODEL_BJJ.CR_DEVICE_WARE, filter(@getenv('transaction','csn')>14115053976261);

MAP ADMIN.*, TARGET bj.*;

GGSCI (zxm) 38>

====测试记录==

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

E:\ogg\soft>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 11月 26 13:38:04 2015

SQL> conn /as sysdba

已连接。

SQL> disc

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

SQL> conn jyc/jyc

已连接。

SQL> select table_name from user_tables;

TABLE_NAME

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

T_ACCOUNT_LIST

T_SERVICE_ACCOUNT_LIST

测试1

PLSQL_PROFILER_RUNS

PLSQL_PROFILER_UNITS

PLSQL_PROFILER_DATA

T_PARTITION_RANGE_TMP

GYJ

TRUTAB

PT_HIS

TABLE_NAME

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

T_RANGE_RANGE

KXGK_QUERYSTAT_ORDER_LIST

KXGK_QUERYSTAT_ORDER_LIST_STEP

T_PARTITION_RANGE

T_P

T_TASK_SUB

TEST_RP

GK_INFO_HIS_TMP

BILL_DATA_T

FREIGHT_ITEM_T

PT_CUR

TABLE_NAME

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

LOG_ARCH

LOG_ARCH_TEST

P201009

TEST

HSDB

TMP

已选择31行。

SQL> select count(*) from t_p;

COUNT(*)

----------

SQL> select * from t_p;

ID NAME

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

11 a

12 b

13 c

SQL> conn admin/admin

已连接。

SQL> disc

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

SQL> conn jyc/jyc

已连接。

SQL> create table t_p1 as select * from t_p;

表已创建。

SQL> select * from t_p;

ID NAME

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

11 a

12 b

13 c

SQL> insert into t_p1 values(10,'d');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_p1;

ID NAME

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

11 a

12 b

13 c

10 d

SQL> conn admin/admin

已连接。

SQL> create table t as select * from jyc.t_p;

表已创建。

SQL> select * from t;

ID NAME

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

11 a

12 b

13 c

SQL> select * from bj.t;

ID NAME

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

11 a

12 b

13 c

SQL> conn bj/bj

已连接。

SQL> insert into t values(10,'d');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select count(*) from t;

COUNT(*)

----------

SQL> select count(*) from admin.t;

COUNT(*)

----------

SQL> conn admin/admin

已连接。

SQL> create table t1 as select * from jyc.t where 1>1;

表已创建。

SQL> select * from t1;

未选定行

SQL> select * from bj.t1;

未选定行

SQL> insert into t1(id,name) select id,name from t;

已创建 3 行。

SQL> commit;

提交完成。

SQL> select * from t1;

ID NAME DEMO

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

11 a

12 b

13 c

SQL> select * from bj.t1;

ID NAME DEMO

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

11 a

12 b

13 c

SQL> select * from bj.t;

ID NAME

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

11 a

12 b

13 c

10 d

SQL> select * from t;

ID NAME

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

11 a

12 b

13 c

SQL> show parameter name;

NAME TYPE VALUE

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

db_file_name_convert string

db_name string jyc

db_unique_name string jyc

global_names boolean FALSE

instance_name string jyc

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string jyc

SQL> show user;

USER 为 "ADMIN"

SQL> create table t2 as select * from t;

表已创建。

SQL> select count(*) from t2;

COUNT(*)

----------

SQL> select count(*) from bj.t2;

COUNT(*)

----------

SQL> create table t3 as select * from t1 where 1>1;

表已创建。

SQL> select count(*) from t1;

COUNT(*)

----------

SQL> select count(*) from t3;

COUNT(*)

----------

SQL> select count(*) from bj.t3;

COUNT(*)

----------

SQL> insert into t3(id,name) select id,name from t2;

已创建 3 行。

SQL> commit;

提交完成。

SQL> select count(*) from bj.t3;

COUNT(*)

----------

SQL> select count(*) from bj.t3;

COUNT(*)

----------

SQL> select count(*) from bj.t2;

COUNT(*)

----------

SQL> select count(*) from t3;

COUNT(*)

----------

SQL> select count(*) from bj.t3;

COUNT(*)

----------

SQL>

===测试结论：

源端insert into table (values) select * from table会抽取源端本地的insert记录，然后推送到目标端，目标端应用trail队列文件，数据保持和源端一致。

源端采用create table t3 as select * from table2，会抽取各自的table2表，如果两边的table2记录本身不一致，那么t3也将都不一致。所以如果用create table as创建表需注意源表本身数据是否一致。同时create table需注意默认值等情况，一般建议用pl/sql提取相关脚本。

Microsoft Windows [版本 6.1.7601]

C:\Users\Administrator>e:

E:\>cd E:\ogg\soft\dirdat

E:\ogg\soft\dirdat>cd ..

E:\ogg\soft>logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 11.2.1.0.28 19820451 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_150116.0606

Logdump 1 >open ./dirdat/ps000000

Current LogTrail is E:\ogg\soft\dirdat\ps000000

Logdump 2 >pos 0

Reading forward from RBA 0

Logdump 3 >detail on

Logdump 4 >n

2015/11/26 13:41:22.972.000 FileHeader Len 1039 RBA 0

Name: *FileHeader*

3000 01d2 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...

0003 3200 0004 2000 0000 3300 0008 02f2 4b9e 7b28 | ..2... ...3.....K.{(

0960 3400 0022 0020 7572 693a 7a78 6d3a 6472 6976 | .`4..". uri:zxm:driv

652d 453a 6f67 673a 736f 6674 3a50 534f 5552 4345 | e-E:ogg:soft:PSOURCE

3500 0026 3500 0022 0020 7572 693a 7a78 6d3a 6472 | 5..&5..". uri:zxm:dr

6976 652d 453a 6f67 673a 736f 6674 3a45 534f 5552 | ive-E:ogg:soft:ESOUR

4345 3600 001d 001b 453a 5c6f 6767 5c73 6f66 745c | CE6.....E:\ogg\soft\

Logdump 5 >n

2015/11/26 13:41:10.001.000 DDLOP Len 1202 RBA 1047

Name:

After Image: Partition 0 G s

2c43 353d 2732 3127 2c2c 4237 3d27 3231 272c 2c42 | ,C5='21',,B7='21',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5427 2c2c 4331 323d 2727 2c2c 4331 333d 2727 | ='T',,C12='',,C13=''

2c2c 4235 3d27 5441 424c 4527 2c2c 4236 3d27 4352 | ,,B5='TABLE',,B6='CR

4541 5445 272c 2c42 383d 274f 4747 4144 4d2e 4747 | EATE',,B8='OGGADM.GG

535f 4444 4c5f 4849 5354 272c 2c42 393d 2741 444d | S_DDL_HIST',,B9='ADM

494e 272c 2c43 373d 2731 312e 322e 302e 332e 3027 | IN',,C7='11.2.0.3.0'

Logdump 6 >n

2015/11/26 13:43:48.000.000 DDLOP Len 1212 RBA 2389

Name:

After Image: Partition 0 G s

2c43 353d 2732 3627 2c2c 4237 3d27 3236 272c 2c42 | ,C5='26',,B7='26',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5431 272c 2c43 3132 3d27 272c 2c43 3133 3d27 | ='T1',,C12='',,C13='

272c 2c42 353d 2754 4142 4c45 272c 2c42 363d 2743 | ',,B5='TABLE',,B6='C

5245 4154 4527 2c2c 4238 3d27 4f47 4741 444d 2e47 | REATE',,B8='OGGADM.G

4753 5f44 444c 5f48 4953 5427 2c2c 4239 3d27 4144 | GS_DDL_HIST',,B9='AD

4d49 4e27 2c2c 4337 3d27 3131 2e32 2e30 2e33 2e30 | MIN',,C7='11.2.0.3.0

Logdump 7 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3743

Name: ADMIN.T1

After Image: Partition 4 G b

0000 0006 0000 0002 3131 0001 0005 0000 0001 6100 | ........11........a.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 8 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3883

Name: ADMIN.T1

After Image: Partition 4 G m

0000 0006 0000 0002 3132 0001 0005 0000 0001 6200 | ........12........b.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 9 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3997

Name: ADMIN.T1

After Image: Partition 4 G e

0000 0006 0000 0002 3133 0001 0005 0000 0001 6300 | ........13........c.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 10 >n

2015/11/26 13:48:48.000.000 DDLOP Len 1198 RBA 4111

Name:

After Image: Partition 0 G s

2c43 353d 2733 3127 2c2c 4237 3d27 3331 272c 2c42 | ,C5='31',,B7='31',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5432 272c 2c43 3132 3d27 272c 2c43 3133 3d27 | ='T2',,C12='',,C13='

272c 2c42 353d 2754 4142 4c45 272c 2c42 363d 2743 | ',,B5='TABLE',,B6='C

5245 4154 4527 2c2c 4238 3d27 4f47 4741 444d 2e47 | REATE',,B8='OGGADM.G

4753 5f44 444c 5f48 4953 5427 2c2c 4239 3d27 4144 | GS_DDL_HIST',,B9='AD

4d49 4e27 2c2c 4337 3d27 3131 2e32 2e30 2e33 2e30 | MIN',,C7='11.2.0.3.0

Logdump 11 >n

2015/11/26 13:50:08.000.000 DDLOP Len 1209 RBA 5451

Name:

After Image: Partition 0 G s

2c43 353d 2733 3627 2c2c 4237 3d27 3336 272c 2c42 | ,C5='36',,B7='36',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5433 272c 2c43 3132 3d27 272c 2c43 3133 3d27 | ='T3',,C12='',,C13='

272c 2c42 353d 2754 4142 4c45 272c 2c42 363d 2743 | ',,B5='TABLE',,B6='C

5245 4154 4527 2c2c 4238 3d27 4f47 4741 444d 2e47 | REATE',,B8='OGGADM.G

4753 5f44 444c 5f48 4953 5427 2c2c 4239 3d27 4144 | GS_DDL_HIST',,B9='AD

4d49 4e27 2c2c 4337 3d27 3131 2e32 2e30 2e33 2e30 | MIN',,C7='11.2.0.3.0

Logdump 12 >n

2015/11/26 13:51:08.000.000 Insert Len 27 RBA 6802

Name: ADMIN.T3

After Image: Partition 4 G b

0000 0006 0000 0002 3131 0001 0005 0000 0001 6100 | ........11........a.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 13 >n

2015/11/26 13:51:08.000.000 Insert Len 27 RBA 6942

Name: ADMIN.T3

After Image: Partition 4 G m

0000 0006 0000 0002 3132 0001 0005 0000 0001 6200 | ........12........b.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 14 >n

2015/11/26 13:51:08.000.000 Insert Len 27 RBA 7056

Name: ADMIN.T3

After Image: Partition 4 G e

0000 0006 0000 0002 3133 0001 0005 0000 0001 6300 | ........13........c.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 15 >n

Logdump 16 >n

Logdump 18 >pos 0

Reading forward from RBA 0

Logdump 19 >n

2015/11/26 13:41:22.972.000 FileHeader Len 1039 RBA 0

Name: *FileHeader*

3000 01d2 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...

0003 3200 0004 2000 0000 3300 0008 02f2 4b9e 7b28 | ..2... ...3.....K.{(

0960 3400 0022 0020 7572 693a 7a78 6d3a 6472 6976 | .`4..". uri:zxm:driv

652d 453a 6f67 673a 736f 6674 3a50 534f 5552 4345 | e-E:ogg:soft:PSOURCE

3500 0026 3500 0022 0020 7572 693a 7a78 6d3a 6472 | 5..&5..". uri:zxm:dr

6976 652d 453a 6f67 673a 736f 6674 3a45 534f 5552 | ive-E:ogg:soft:ESOUR

4345 3600 001d 001b 453a 5c6f 6767 5c73 6f66 745c | CE6.....E:\ogg\soft\

Logdump 20 >help

FC [ | ] - Edit previous command

HISTORY - List previous commands

OPEN | FROM - Open a Log file

RECORD | REC - Display audit record

NEXT [ ] - Display next data record

SKIP [ ] [FILTER] - Skip down records

FILTER - Apply filter during skip

COUNT - Count the records in the file

[START[time] ,]

[END[time] ,]

[INT[erval] ,]

[LOG[trail] ,]

[FILE ,]

[DETAIL ]

format is

[[yy]yy-mm-dd] [hh[:mm][:ss]]

POSITION [ | FIRST | LAST | EOF ] - Set position in file

REVerse | FORward - Set read direction

RECLEN [ ] - Sets max output length

EXIT | QUIT - Exit the program

FILES | FI | DIR - Display filenames

ENV - Show current settings

VOLUME | VOL | V - Change default volume

DEBUG - Enter the debugger

GHDR ON | OFF - Toggle GHDR display

DETAIL ON | OFF | DATA - Toggle detailed data display

RECLEN - Set data display length

SCANFORHEADER (SFH) [PREV] - Search for the start of a header

SCANFORTYPE (SFT) - Find the next record of

[,]

SCANFORRBA (SFR) - Find the next record with

- syskey = -1 scans for next record

SCANFORTIME (SFTS) - Find the next record with timestamp

[,]

format is

[[yy]yy-mm-dd] [hh[:mm][:ss]]

SCANFORENDTRANS (SFET) - Find the end of the current transaction

SCANFORNEXTTRANS (SFNT) - Find start of the next transaction

SHOW

- Display internal information

[OPEN] - list open files

[TIME] - print current time in various formats

[ENV] - show current environment

[RECTYPE] - show list of record types

[FILTER] - show active filter items

BIO

- Set LargeBlock I/O info

[ON] - Enable LargeBlock I/O (default)

[OFF] - Disable LargeBlock I/O

[BLOCK ]- Set LargeBlock I/O size

TIMEOFFSET

- Set the time offset from GMT

[LOCAL] - Use local time

[GMT] - Use GMT time

[GMT +/- hh[:mm]] - Offset +/- from GMT

FILTER SHOW

FILTER ENABLE | ON - Enable filtering

FILTER DISABLE | OFF - Disable filtering

FILTER CLEAR [ | ]

FILTER MATCH ANY | ALL

FILTER [INClude | EXCLude]

are

RECTYPE

STRING [BOTH] // []

HEX []

TRANSID

FILENAME

PROCESS

INT16 <16-bit integer>

INT32 <32-bit integer>

INT64 <64-bit integer>

STARTTIME

ENDTIME

SYSKEY [] <32/64-bit syskey>

SYSKEYLEN [] []

TRANSIND []

UNDOFLAG []

RECLEN []

AUDITRBA []

ANSINAME

GGSTOKEN [] []

USERTOKEN [] []

CSN | LogCSN [] []

:, ie 0:231

=, ==, !=, <>, <, >, <=, >= EQ, GT, LE, GE, LE, NE

X [string] - Execute

TRANSHIST nnnn - Set size of transaction history

TRANSRECLIMIT nnnn - Set low record count threshold

TRANSBYTELIMIT nnnn - Set low byte count threshold

LOG {STOP} | { [TO] } - Write a session log

BEGIN - Set next read position using a timestamp

SAVEFILECOMMENT on | OFF - Toggle comment records in a savefile

SAVE [!] - Write data to a savefile

are

nnn RECORDS | nnn BYTES

[NOCOMMENT] - Suppress the Comment header/trailer recs, Default

[COMMENT] - Insert Comment header/trailer recs

[OLDFORMAT] - Force oldformat records

[NEWFORMAT] - Force newformat records

[TRUNCATE ] - purgedata an existing savefile

[EXT ( , [,])] - Savefile Extent sizes on NSK

[MEGabytes ] - For extent size calculation

[TRANSIND ] - Set the transind field

[COMMITTS ] - Set the committs field

USERTOKEN on | OFF | detail - Show user token info

HEADERTOKEN on | OFF | detail - Show header token info

GGSTOKEN on | OFF | detail - Show GGS token info

FILEHEADER on | OFF | detail - Display file header contents

ASCIIHEADER ON | off - Toggle header charset

EBCDICHEADER on | OFF - Toggle header charset

ASCIIDATA ON | on - Toggle user data charset

EBCDICDATA on | OFF - Toggle user data charset

ASCIIDUMP ON | off - Toggle charset for hex/ascii display

EBCDICDUMP on | OFF - Toggle charset for hex/ascii display

TRAILFORMAT old | new - Force trail type

PRINTMXCOLUMNINFO on | OFF - Toggle SQL/MX columninfo display

TMFBEFOREIMAGE on | OFF - Toggle display of TMF before images

FLOAT - Interpret a floating point number

[FORMAT ] - sprintf format default %f

Logdump 21 >reclen 1024

Reclen set to 1024

Logdump 22 >pos 0

Reading forward from RBA 0

Logdump 23 >n

2015/11/26 13:41:22.972.000 FileHeader Len 1039 RBA 0

Name: *FileHeader*

3000 01d2 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...

0003 3200 0004 2000 0000 3300 0008 02f2 4b9e 7b28 | ..2... ...3.....K.{(

0960 3400 0022 0020 7572 693a 7a78 6d3a 6472 6976 | .`4..". uri:zxm:driv

652d 453a 6f67 673a 736f 6674 3a50 534f 5552 4345 | e-E:ogg:soft:PSOURCE

3500 0026 3500 0022 0020 7572 693a 7a78 6d3a 6472 | 5..&5..". uri:zxm:dr

6976 652d 453a 6f67 673a 736f 6674 3a45 534f 5552 | ive-E:ogg:soft:ESOUR

4345 3600 001d 001b 453a 5c6f 6767 5c73 6f66 745c | CE6.....E:\ogg\soft\

6469 7264 6174 5c70 7330 3030 3030 3037 0000 0101 | dirdat\ps0000007....

3800 0004 0000 0000 39ff 0008 0000 0000 0000 0000 | 8.......9...........

3aff 0081 0000 0000 0000 0000 0000 0000 0000 0000 | :...................

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 0000 0000 003b ff00 8100 0000 | .............;......

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 3cff 0008 0000 0000 0000 0000 3dff | ......<...........=.

0008 0000 0000 0000 0000 3100 003a 3000 0013 0011 | ..........1..:0.....

4d69 6372 6f73 6f66 7420 5769 6e64 6f77 7331 0000 | Microsoft Windows1..

0500 037a 786d 3200 0005 0003 362e 3133 0000 0400 | ...zxm2.....6.13....

0237 2034 0000 0500 0378 3634 3200 0166 3000 0002 | .7 4.....x642..f0...

0007 3100 0005 0003 4a59 4332 0000 0500 036a 7963 | ..1.....JYC2.....jyc

3300 0004 0000 1389 3400 0002 000b 3500 0002 0002 | 3.......4.....5.....

3600 00f0 00ee 4f72 6163 6c65 2044 6174 6162 6173 | 6.....Oracle Databas

6520 3131 6720 456e 7465 7270 7269 7365 2045 6469 | e 11g Enterprise Edi

7469 6f6e 2052 656c 6561 7365 2031 312e 322e 302e | tion Release 11.2.0.

332e 3020 2d20 3634 6269 7420 5072 6f64 7563 7469 | 3.0 - 64bit Producti

6f6e 0a50 4c2f 5351 4c20 5265 6c65 6173 6520 3131 | on.PL/SQL Release 11

2e32 2e30 2e33 2e30 202d 2050 726f 6475 6374 696f | .2.0.3.0 - Productio

6e0a 434f 5245 0931 312e 322e 302e 332e 3009 5072 | n.CORE.11.2.0.3.0.Pr

6f64 7563 7469 6f6e 0a54 4e53 2066 6f72 2036 342d | oduction.TNS for 64-

6269 7420 5769 6e64 6f77 733a 2056 6572 7369 6f6e | bit Windows: Version

2031 312e 322e 302e 332e 3020 2d20 5072 6f64 7563 | 11.2.0.3.0 - Produc

7469 6f6e 0a4e 4c53 5254 4c20 5665 7273 696f 6e20 | tion.NLSRTL Version

3131 2e32 2e30 2e33 2e30 202d 2050 726f 6475 6374 | 11.2.0.3.0 - Product

696f 6e0a 3700 0004 0000 1389 3800 000c 000a 3131 | ion.7.......8.....11

2e32 2e30 2e33 2e30 3900 0004 0000 0001 3a00 0002 | .2.0.3.09.......:...

0000 3b00 0004 0000 0001 3c00 0014 0000 0010 1414 | ..;.......<.........

1414 1414 1414 1414 1414 1114 1414 3300 0081 3000 | ..............3...0.

0009 0007 4553 4f55 5243 4531 0000 0200 0332 0000 | ....ESOURCE1.....2..

0200 0b33 0000 0200 0234 0000 0200 0135 0000 0200 | ...3.....4.....5....

0036 0000 0200 1c37 0000 4c00 4a56 6572 7369 6f6e | .6.....7..L.JVersion

2031 312e 322e 312e 302e 3238 2031 3938 3230 3435 | 11.2.1.0.28 1982045

3120 4f47 4743 4f52 455f 3131 2e32 2e31 2e30 2e30 | 1 OGGCORE_11.2.1.0.0

4f47 4742 505f 504c 4154 464f 524d 535f 3135 3031 | OGGBP_PLATFORMS_1501

3136 2e30 | 16.0

Logdump 24 >n

2015/11/26 13:41:10.001.000 DDLOP Len 1202 RBA 1047

Name:

After Image: Partition 0 G s

2c43 353d 2732 3127 2c2c 4237 3d27 3231 272c 2c42 | ,C5='21',,B7='21',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5427 2c2c 4331 323d 2727 2c2c 4331 333d 2727 | ='T',,C12='',,C13=''

2c2c 4235 3d27 5441 424c 4527 2c2c 4236 3d27 4352 | ,,B5='TABLE',,B6='CR

4541 5445 272c 2c42 383d 274f 4747 4144 4d2e 4747 | EATE',,B8='OGGADM.GG

535f 4444 4c5f 4849 5354 272c 2c42 393d 2741 444d | S_DDL_HIST',,B9='ADM

494e 272c 2c43 373d 2731 312e 322e 302e 332e 3027 | IN',,C7='11.2.0.3.0'

2c2c 4338 3d27 3131 2e32 2e30 2e30 2e30 272c 2c43 | ,,C8='11.2.0.0.0',,C

393d 2727 2c2c 4331 303d 2731 272c 2c43 3131 3d27 | 9='',,C10='1',,C11='

6a79 6327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 | jyc',,G3='NONUNIQUE'

2c2c 4331 343d 274e 4f27 2c2c 4332 303d 274e 4f27 | ,,C14='NO',,C20='NO'

2c2c 4331 353d 274e 4f27 2c2c 4331 393d 2731 3727 | ,,C15='NO',,C19='17'

2c2c 4331 3728 2731 2729 3d27 4e4c 535f 4c41 4e47 | ,,C17('1')='NLS_LANG

5541 4745 272c 2c43 3138 2827 3127 293d 2753 494d | UAGE',,C18('1')='SIM

504c 4946 4945 4420 4348 494e 4553 4527 2c2c 4331 | PLIFIED CHINESE',,C1

3728 2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 | 7('2')='NLS_TERRITOR

5927 2c2c 4331 3828 2732 2729 3d27 4348 494e 4127 | Y',,C18('2')='CHINA'

2c2c 4331 3728 2733 2729 3d27 4e4c 535f 4355 5252 | ,,C17('3')='NLS_CURR

454e 4359 272c 2c43 3138 2827 3327 293d 27ef bfa5 | ENCY',,C18('3')='...

272c 2c43 3137 2827 3427 293d 274e 4c53 5f49 534f | ',,C17('4')='NLS_ISO

5f43 5552 5245 4e43 5927 2c2c 4331 3828 2734 2729 | _CURRENCY',,C18('4')

3d27 4348 494e 4127 2c2c 4331 3728 2735 2729 3d27 | ='CHINA',,C17('5')='

4e4c 535f 4e55 4d45 5249 435f 4348 4152 4143 5445 | NLS_NUMERIC_CHARACTE

5253 272c 2c43 3138 2827 3527 293d 272e 5c2c 272c | RS',,C18('5')='.\,',

2c43 3137 2827 3627 293d 274e 4c53 5f43 414c 454e | ,C17('6')='NLS_CALEN

4441 5227 2c2c 4331 3828 2736 2729 3d27 4752 4547 | DAR',,C18('6')='GREG

4f52 4941 4e27 2c2c 4331 3728 2737 2729 3d27 4e4c | ORIAN',,C17('7')='NL

535f 4441 5445 5f46 4f52 4d41 5427 2c2c 4331 3828 | S_DATE_FORMAT',,C18(

2737 2729 3d27 4444 2d4d 4f4e 2d52 5227 2c2c 4331 | '7')='DD-MON-RR',,C1

3728 2738 2729 3d27 4e4c 535f 4441 5445 5f4c 414e | 7('8')='NLS_DATE_LAN

4755 4147 4527 2c2c 4331 3828 2738 2729 3d27 5349 | GUAGE',,C18('8')='SI

4d50 4c49 4649 4544 2043 4849 4e45 5345 272c 2c43 | MPLIFIED CHINESE',,C

3137 2827 3927 293d 274e 4c53 5f53 4f52 5427 2c2c | 17('9')='NLS_SORT',,

4331 3828 2739 2729 3d27 4249 4e41 5259 272c 2c43 | C18('9')='BINARY',,C

3137 2827 3130 2729 3d27 4e4c 535f 5449 4d45 5f46 | 17('10')='NLS_TIME_F

4f52 4d41 5427 2c2c 4331 3828 2731 3027 293d 2748 | ORMAT',,C18('10')='H

482e 4d49 2e53 5358 4646 2041 4d27 2c2c 4331 3728 | H.MI.SSXFF AM',,C17(

2731 3127 293d 274e 4c53 5f54 494d 4553 5441 4d50 | '11')='NLS_TIMESTAMP

5f46 4f52 4d41 5427 2c2c 4331 3828 2731 3127 293d | _FORMAT',,C18('11')=

2744 442d 4d4f 4e2d 5252 2048 482e 4d49 2e53 5358 | 'DD-MON-RR HH.MI.SSX

4646 2041 4d27 2c2c 4331 3728 2731 3227 293d 274e | FF AM',,C17('12')='N

4c53 5f54 494d 455f 545a 5f46 4f52 4d41 5427 2c2c | LS_TIME_TZ_FORMAT',,

4331 3828 2731 3227 293d 2748 482e 4d49 2e53 5358 | C18('12')='HH.MI.SSX

4646 2041 4d20 545a 5227 2c2c 4331 3728 2731 3327 | FF AM TZR',,C17('13'

293d 274e 4c53 5f54 494d 4553 5441 4d50 5f54 5a5f | )='NLS_TIMESTAMP_TZ_

464f 524d 4154 272c 2c43 3138 2827 3133 2729 3d27 | FORMAT',,C18('13')='

4444 2d4d 4f4e 2d52 5220 4848 2e4d 492e 5353 5846 | DD-MON-RR HH.MI.SSXF

4620 414d 2054 5a52 272c 2c43 3137 2827 3134 2729 | F AM TZR',,C17('14')

3d27 4e4c 535f 4455 414c 5f43 5552 5245 4e43 5927 | ='NLS_DUAL_CURRENCY'

2c2c 4331 3828 2731 3427 293d 27ef bfa5 272c 2c43 | ,,C18('14')='...',,C

3137 2827 3135 2729 3d27 4e4c 535f 434f 4d50 272c | 17('15')='NLS_COMP',

2c43 3138 | ,C18

Logdump 25 >n

2015/11/26 13:43:48.000.000 DDLOP Len 1212 RBA 2389

Name:

After Image: Partition 0 G s

2c43 353d 2732 3627 2c2c 4237 3d27 3236 272c 2c42 | ,C5='26',,B7='26',,B

323d 2727 2c2c 4233 3d27 4144 4d49 4e27 2c2c 4234 | 2='',,B3='ADMIN',,B4

3d27 5431 272c 2c43 3132 3d27 272c 2c43 3133 3d27 | ='T1',,C12='',,C13='

272c 2c42 353d 2754 4142 4c45 272c 2c42 363d 2743 | ',,B5='TABLE',,B6='C

5245 4154 4527 2c2c 4238 3d27 4f47 4741 444d 2e47 | REATE',,B8='OGGADM.G

4753 5f44 444c 5f48 4953 5427 2c2c 4239 3d27 4144 | GS_DDL_HIST',,B9='AD

4d49 4e27 2c2c 4337 3d27 3131 2e32 2e30 2e33 2e30 | MIN',,C7='11.2.0.3.0

272c 2c43 383d 2731 312e 322e 302e 302e 3027 2c2c | ',,C8='11.2.0.0.0',,

4339 3d27 272c 2c43 3130 3d27 3127 2c2c 4331 313d | C9='',,C10='1',,C11=

276a 7963 272c 2c47 333d 274e 4f4e 554e 4951 5545 | 'jyc',,G3='NONUNIQUE

272c 2c43 3134 3d27 4e4f 272c 2c43 3230 3d27 4e4f | ',,C14='NO',,C20='NO

272c 2c43 3135 3d27 4e4f 272c 2c43 3139 3d27 3137 | ',,C15='NO',,C19='17

272c 2c43 3137 2827 3127 293d 274e 4c53 5f4c 414e | ',,C17('1')='NLS_LAN

4755 4147 4527 2c2c 4331 3828 2731 2729 3d27 5349 | GUAGE',,C18('1')='SI

4d50 4c49 4649 4544 2043 4849 4e45 5345 272c 2c43 | MPLIFIED CHINESE',,C

3137 2827 3227 293d 274e 4c53 5f54 4552 5249 544f | 17('2')='NLS_TERRITO

5259 272c 2c43 3138 2827 3227 293d 2743 4849 4e41 | RY',,C18('2')='CHINA

272c 2c43 3137 2827 3327 293d 274e 4c53 5f43 5552 | ',,C17('3')='NLS_CUR

5245 4e43 5927 2c2c 4331 3828 2733 2729 3d27 efbf | RENCY',,C18('3')='..

a527 2c2c 4331 3728 2734 2729 3d27 4e4c 535f 4953 | .',,C17('4')='NLS_IS

4f5f 4355 5252 454e 4359 272c 2c43 3138 2827 3427 | O_CURRENCY',,C18('4'

293d 2743 4849 4e41 272c 2c43 3137 2827 3527 293d | )='CHINA',,C17('5')=

274e 4c53 5f4e 554d 4552 4943 5f43 4841 5241 4354 | 'NLS_NUMERIC_CHARACT

4552 5327 2c2c 4331 3828 2735 2729 3d27 2e5c 2c27 | ERS',,C18('5')='.\,'

2c2c 4331 3728 2736 2729 3d27 4e4c 535f 4341 4c45 | ,,C17('6')='NLS_CALE

4e44 4152 272c 2c43 3138 2827 3627 293d 2747 5245 | NDAR',,C18('6')='GRE

474f 5249 414e 272c 2c43 3137 2827 3727 293d 274e | GORIAN',,C17('7')='N

4c53 5f44 4154 455f 464f 524d 4154 272c 2c43 3138 | LS_DATE_FORMAT',,C18

2827 3727 293d 2744 442d 4d4f 4e2d 5252 272c 2c43 | ('7')='DD-MON-RR',,C

3137 2827 3827 293d 274e 4c53 5f44 4154 455f 4c41 | 17('8')='NLS_DATE_LA

4e47 5541 4745 272c 2c43 3138 2827 3827 293d 2753 | NGUAGE',,C18('8')='S

494d 504c 4946 4945 4420 4348 494e 4553 4527 2c2c | IMPLIFIED CHINESE',,

4331 3728 2739 2729 3d27 4e4c 535f 534f 5254 272c | C17('9')='NLS_SORT',

2c43 3138 2827 3927 293d 2742 494e 4152 5927 2c2c | ,C18('9')='BINARY',,

4331 3728 2731 3027 293d 274e 4c53 5f54 494d 455f | C17('10')='NLS_TIME_

464f 524d 4154 272c 2c43 3138 2827 3130 2729 3d27 | FORMAT',,C18('10')='

4848 2e4d 492e 5353 5846 4620 414d 272c 2c43 3137 | HH.MI.SSXFF AM',,C17

2827 3131 2729 3d27 4e4c 535f 5449 4d45 5354 414d | ('11')='NLS_TIMESTAM

505f 464f 524d 4154 272c 2c43 3138 2827 3131 2729 | P_FORMAT',,C18('11')

3d27 4444 2d4d 4f4e 2d52 5220 4848 2e4d 492e 5353 | ='DD-MON-RR HH.MI.SS

5846 4620 414d 272c 2c43 3137 2827 3132 2729 3d27 | XFF AM',,C17('12')='

4e4c 535f 5449 4d45 5f54 5a5f 464f 524d 4154 272c | NLS_TIME_TZ_FORMAT',

2c43 3138 2827 3132 2729 3d27 4848 2e4d 492e 5353 | ,C18('12')='HH.MI.SS

5846 4620 414d 2054 5a52 272c 2c43 3137 2827 3133 | XFF AM TZR',,C17('13

2729 3d27 4e4c 535f 5449 4d45 5354 414d 505f 545a | ')='NLS_TIMESTAMP_TZ

5f46 4f52 4d41 5427 2c2c 4331 3828 2731 3327 293d | _FORMAT',,C18('13')=

2744 442d 4d4f 4e2d 5252 2048 482e 4d49 2e53 5358 | 'DD-MON-RR HH.MI.SSX

4646 2041 4d20 545a 5227 2c2c 4331 3728 2731 3427 | FF AM TZR',,C17('14'

293d 274e 4c53 5f44 5541 4c5f 4355 5252 454e 4359 | )='NLS_DUAL_CURRENCY

272c 2c43 3138 2827 3134 2729 3d27 efbf a527 2c2c | ',,C18('14')='...',,

4331 3728 2731 3527 293d 274e 4c53 5f43 4f4d 5027 | C17('15')='NLS_COMP'

2c2c 4331 | ,,C1

Logdump 26 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3743

Name: ADMIN.T1

After Image: Partition 4 G b

0000 0006 0000 0002 3131 0001 0005 0000 0001 6100 | ........11........a.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 27 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3883

Name: ADMIN.T1

After Image: Partition 4 G m

0000 0006 0000 0002 3132 0001 0005 0000 0001 6200 | ........12........b.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 28 >n

2015/11/26 13:46:48.000.000 Insert Len 27 RBA 3997

Name: ADMIN.T1

After Image: Partition 4 G e

0000 0006 0000 0002 3133 0001 0005 0000 0001 6300 | ........13........c.

0200 04ff ff00 00 | .......

Column 0 (x0000), Len 6 (x0006)

Column 1 (x0001), Len 5 (x0005)

Column 2 (x0002), Len 4 (x0004)

Logdump 29 >n