CAUSE:
Goldent Gate DDL is enabled on database. Trigger sys.GGS_DDL_TRIGGER_BEFORE will be fired for utlrp, but the trigger is failed because some Golden Gate object is invalid.
Solution:
1. disable the trigger as sysdba
alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;
2. run utlrp
@?/rdbms/admin/utlrp.sql
3. enable the trigger as sysdba
alter trigger sys.GGS_DDL_TRIGGER_BEFORE enable ;
reference:
http://harikrishnakm.wordpress.com/2013/08/19/catuppst-sql-fails-with-gguser-ddlreplication-does-not-exist-after-upgrading-the-database/
Tuesday, November 5, 2013
Monday, October 14, 2013
Duplicate Database using RMAN backup without connecting to target for oracle 10g version on windows
PURPOSE:
Duplicate oracle 10g database in new server for testing without connecting to target.
STEPS:
STEP 1. make a backup
1.1 backup database
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_db_%d_%T_%U.rman';
backup as compressed backupset full database;
release channel c1;
}
1.2 backup controlfile
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_ctl_%d_%T_%U.rman';
backup as compressed backupset current controlfile;
release channel c1;
}
1.3 backup required archive logs
run
{
allocate channel c1 type disk format 'F:\rman_test\rman_arch_%d_%T_%U.rman';
backup archivelog sequence between 100 and 200 ;
release channel c1;
}
STEP 2. Create required directory for new database in new host
2.1 data file, log file and tempfile location
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;
mkdir D:ORACLE\ORADATA\TEST
2.2 other locations ( dump dest and archivelog dest)
show parameter dest
STEP 3. copy pfile and make modification
STEP 4. create windows service
radim -new -sid test -intpwd test
STEP 5. start instance
startup mount
STEP 6. restore control file and mount database
rman target /
set dbid=123456789;
restore controlfile from 'F:\rman\RMAN_ctl_test_20131011_95OM4JNU_1_1.RMAN';
alter database mount;
STEP 7. restore database from backup
new location need be specified by using "set new name"
catalog start with 'F:\rman'
rman target /
run
{
set newname for datafile 1 to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
set newname for datafile 2 to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
set newname for datafile 3 to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
set newname for datafile 4 to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
allocate channel c1 type disk format 'F:\rman\\RMAN_DB_%d_%T_%U.RMAN';
restore database ;
release channel c1;
}
STEP 8. rename datafile and logfile
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\USERS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO01.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO02.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO03.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO01B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO01B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO02B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO02B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO03B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO03B.LOG';
STEP 9. recover database to consistent state
rman target /
recover database until sequence 201;
STEP 10. open database with resetlogs and drop, recreate temp file
alter database open resetlogs;
alter tablespace temp add tempfile 'D:\ORACLE\ORADATA\TEST1\TEMP01.DBF' size 3G ;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\TEST\TEMP01.DBF' DROP INCLUDING DATAFILES;
STEP 11. set up network connection
sqlplus sys/test@test as sysdba
STEP 12. copy pfile of test to pfile of test1
chagne db name from test to test1
STEP 13. rename database
orapwd FILE=pwdtest.ora PASSWORD=test
startup mount
nid TARGET=SYS/test@test DBNAME=test1
STEP 14. set oracle_sid as test1 and open database with resetlogs
startup mount
alter database open restlogs;
REFERENCE:
http://dbaregistry.blogspot.com/2011/04/rman-duplicate-without-connecting-to.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbnewid.htm
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmdupdb.htm
https://forums.oracle.com/thread/2421213
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
http://docs.oracle.com/html/B13831_01/admin.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm
http://ss64.com/ora/rman_format_string.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm
http://linuxtechres.blogspot.com/2011/10/how-to-create-oracle-database-service.html
Duplicate oracle 10g database in new server for testing without connecting to target.
STEPS:
STEP 1. make a backup
1.1 backup database
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_db_%d_%T_%U.rman';
backup as compressed backupset full database;
release channel c1;
}
1.2 backup controlfile
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_ctl_%d_%T_%U.rman';
backup as compressed backupset current controlfile;
release channel c1;
}
1.3 backup required archive logs
run
{
allocate channel c1 type disk format 'F:\rman_test\rman_arch_%d_%T_%U.rman';
backup archivelog sequence between 100 and 200 ;
release channel c1;
}
STEP 2. Create required directory for new database in new host
2.1 data file, log file and tempfile location
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;
mkdir D:ORACLE\ORADATA\TEST
2.2 other locations ( dump dest and archivelog dest)
show parameter dest
STEP 3. copy pfile and make modification
STEP 4. create windows service
radim -new -sid test -intpwd test
STEP 5. start instance
startup mount
STEP 6. restore control file and mount database
rman target /
set dbid=123456789;
restore controlfile from 'F:\rman\RMAN_ctl_test_20131011_95OM4JNU_1_1.RMAN';
alter database mount;
STEP 7. restore database from backup
new location need be specified by using "set new name"
catalog start with 'F:\rman'
rman target /
run
{
set newname for datafile 1 to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
set newname for datafile 2 to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
set newname for datafile 3 to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
set newname for datafile 4 to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
allocate channel c1 type disk format 'F:\rman\\RMAN_DB_%d_%T_%U.RMAN';
restore database ;
release channel c1;
}
STEP 8. rename datafile and logfile
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\USERS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO01.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO02.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO03.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO01B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO01B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO02B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO02B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO03B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO03B.LOG';
STEP 9. recover database to consistent state
rman target /
recover database until sequence 201;
STEP 10. open database with resetlogs and drop, recreate temp file
alter database open resetlogs;
alter tablespace temp add tempfile 'D:\ORACLE\ORADATA\TEST1\TEMP01.DBF' size 3G ;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\TEST\TEMP01.DBF' DROP INCLUDING DATAFILES;
STEP 11. set up network connection
sqlplus sys/test@test as sysdba
STEP 12. copy pfile of test to pfile of test1
chagne db name from test to test1
STEP 13. rename database
orapwd FILE=pwdtest.ora PASSWORD=test
startup mount
nid TARGET=SYS/test@test DBNAME=test1
STEP 14. set oracle_sid as test1 and open database with resetlogs
startup mount
alter database open restlogs;
REFERENCE:
http://dbaregistry.blogspot.com/2011/04/rman-duplicate-without-connecting-to.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbnewid.htm
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmdupdb.htm
https://forums.oracle.com/thread/2421213
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
http://docs.oracle.com/html/B13831_01/admin.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm
http://ss64.com/ora/rman_format_string.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm
http://linuxtechres.blogspot.com/2011/10/how-to-create-oracle-database-service.html
Thursday, October 10, 2013
TNS-12518, TNS-12540, TNS-12582, TNS-12615 Listener ERROR caused by swingbench OLTP test
SYMPTOM:
In listener.log file, below error emssage appear.
TNS-12518: TNS:listener could not hand off client connection
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * establish * 12540
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * 12582
TNS-12582: TNS:invalid operation
TNS-12615: TNS:preempt error
CAUSE:
Too many concurrent connection requests are received by listener.
Solution:
Increase the queuesize for listener. There is no more TNS error in log file.
listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=1000)))
TRACE LISTERNER in 11g:
TRACE_LEVLE_LISTENER_NAME=16 ( 0, no trace; 4, user; 10, admin; 16, support)
OTHER THOUGHT:
There is article (1399677.1) on metalink for the same set of errors. That is a bug cause by ADR check.
work around is to set _diag_adr_enabled=FALSE and/or apply Patch:9700115.
It look related to Unpublished Bug:9700115
Reference:
http://docs.oracle.com/cd/B28359_01/network.111/b28317/listener.htm
https://forums.oracle.com/thread/2520109
In listener.log file, below error emssage appear.
TNS-12518: TNS:listener could not hand off client connection
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * establish * 12540
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * 12582
TNS-12582: TNS:invalid operation
TNS-12615: TNS:preempt error
CAUSE:
Too many concurrent connection requests are received by listener.
Solution:
Increase the queuesize for listener. There is no more TNS error in log file.
listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=1000)))
TRACE LISTERNER in 11g:
TRACE_LEVLE_LISTENER_NAME=16 ( 0, no trace; 4, user; 10, admin; 16, support)
OTHER THOUGHT:
There is article (1399677.1) on metalink for the same set of errors. That is a bug cause by ADR check.
work around is to set _diag_adr_enabled=FALSE and/or apply Patch:9700115.
It look related to Unpublished Bug:9700115
Reference:
http://docs.oracle.com/cd/B28359_01/network.111/b28317/listener.htm
https://forums.oracle.com/thread/2520109
Thursday, October 3, 2013
create table with only data using data pump
REQUIREMENT:
Snapshot of data in schema need be stored for comparison. Data snapshot is refreshed from time to time.
EASY SOLUTION:
1. create new schema
create user test1 identified by test;
grant connect to test1;
2. data pump schema including only table
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01.log
3. data pump schema back excluding index, trigger, constraints
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01_imp1.log
4. refresh by truncate and insert if data is some.
select ' truncate table test1.' || table_name || ';' from user_tables;
select ' insert into test1.' || table_name || ' select * from test.' || table_name || ';' from user_tables;
5. refresh by data pump data only if data is large.
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03.log
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03_imp1.log content=data_only
reference:
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Snapshot of data in schema need be stored for comparison. Data snapshot is refreshed from time to time.
EASY SOLUTION:
1. create new schema
create user test1 identified by test;
grant connect to test1;
2. data pump schema including only table
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01.log
3. data pump schema back excluding index, trigger, constraints
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01_imp1.log
4. refresh by truncate and insert if data is some.
select ' truncate table test1.' || table_name || ';' from user_tables;
select ' insert into test1.' || table_name || ' select * from test.' || table_name || ';' from user_tables;
5. refresh by data pump data only if data is large.
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03.log
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03_imp1.log content=data_only
reference:
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Rebuild Standby database through rman restore after open it
1. remove archivelogs from default archivelog location
2. make rman backup files accessible to standby database
3. make rman know backup files
sqlplus> shutdown immediate;
sqlplus> startup mount;
rman target /
rman> catalog start with 'file location directory';
4. restore database
rman > run {
sql 'alter session set optimizer_mode=RULE';
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"';
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT 'file location directory\DBFULL_%d_%U.bkp';
restore database from TAG "backup tag" ;
RELEASE CHANNEL d1;
}
5. recover database
5.1 use controlfile before open database
rman target /
rman> recover database until sequence ?????;
5.2 use controlfile after open database
rman target /
rman> list incarnation;
rman> reset database to incarnation 1; ( previous number)
rman> recover database unitl seequnece ????;
5.3 test standby database
sqlplus / as sysdba
sqlplus> alter database open;
sqlplus> select name, host_name, open_mode, database_role from v$database, v$instance;
reference:
http://oracledb-admin.blogspot.com/2011/10/ora-19909-datafile-1-belongs-to-orphan.html
http://www.dba-oracle.com/bk_recover_database_until_cancel_tips.htm
2. make rman backup files accessible to standby database
3. make rman know backup files
sqlplus> shutdown immediate;
sqlplus> startup mount;
rman target /
rman> catalog start with 'file location directory';
4. restore database
rman > run {
sql 'alter session set optimizer_mode=RULE';
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"';
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT 'file location directory\DBFULL_%d_%U.bkp';
restore database from TAG "backup tag" ;
RELEASE CHANNEL d1;
}
5. recover database
5.1 use controlfile before open database
rman target /
rman> recover database until sequence ?????;
5.2 use controlfile after open database
rman target /
rman> list incarnation;
rman> reset database to incarnation 1; ( previous number)
rman> recover database unitl seequnece ????;
5.3 test standby database
sqlplus / as sysdba
sqlplus> alter database open;
sqlplus> select name, host_name, open_mode, database_role from v$database, v$instance;
reference:
http://oracledb-admin.blogspot.com/2011/10/ora-19909-datafile-1-belongs-to-orphan.html
http://www.dba-oracle.com/bk_recover_database_until_cancel_tips.htm
Wednesday, September 25, 2013
GoldenGate (GG) replicat abended process troubleshooting.
GoldenGate (GG) replicat abended process troubleshooting.
1. Try to start the replicate
ggsci > start replicate TEST
2. check the replicate
ggsci > info all
It should be abended
3. check the process report
cd $GG_DIR/bin/dirrpt
less test.rpt
4. find the trail file and RBA from report
5. get sql statement using logdump
/*
configure below setting on logdump
fileheader detail
ghdr on
detail on
usertoken detail
reclen 128
*/
cd $GG_DIR/bin
logdump
logdump> open app/oracle/gg/trail/test/dirdat/pt0001
logdump> pos 100000
logdump> fileheader detail
logdump> ghdr on
logdump> detail on
logdump> usertoken detail
logdump> reclen 128
logdump> n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 1577 (x0629) IO Time : 2013/09/24 23:15:09.334.111
IOType : 160 (xa0) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2013/09/24 23:15:09.334.111 DDLOP Len 1000 RBA 100000
Name:
After Image: Partition 0 G s
2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'
/*
reset reclen to record length according to output
*/
logdump> reclen 1000
/*
position back to 100000
*/
logdump> pos 100000
logdump> n
/*
you will have output for whole record. The left Panel C1 variable is the SQL statement.
you can try it to find out the real error
*/
6. fixed the issue and restart the replicate
how to fix depends on errors and database settings and even data.
7. skip the transaction and restart the replicate
if you find out that the transaction can be skip.
find RBA position for next record using logdump
logdump> pos 100000
logdump> n
logdump> n
2013/09/24 23:15:09.334.111 DDLOP Len 115 RBA 101000
Name:
After Image: Partition 0 G s
2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'
ggsci> alter replicat test, extrba 101000
ggsci>info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED TEST 16:34:27 00:45:14
ggsci> start replicat test
ggsci> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TEST 16:34:27 00:45:14
reference:
http://blog.flimatech.com/2012/03/24/how-to-find-the-transaction-that-abended-oracle-goldengate/
http://satya-dba.blogspot.com/2012/02/ggsci-goldengate-command-interpreter.html
1. Try to start the replicate
ggsci > start replicate TEST
2. check the replicate
ggsci > info all
It should be abended
3. check the process report
cd $GG_DIR/bin/dirrpt
less test.rpt
4. find the trail file and RBA from report
5. get sql statement using logdump
/*
configure below setting on logdump
fileheader detail
ghdr on
detail on
usertoken detail
reclen 128
*/
cd $GG_DIR/bin
logdump
logdump> open app/oracle/gg/trail/test/dirdat/pt0001
logdump> pos 100000
logdump> fileheader detail
logdump> ghdr on
logdump> detail on
logdump> usertoken detail
logdump> reclen 128
logdump> n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 1577 (x0629) IO Time : 2013/09/24 23:15:09.334.111
IOType : 160 (xa0) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2013/09/24 23:15:09.334.111 DDLOP Len 1000 RBA 100000
Name:
After Image: Partition 0 G s
2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'
/*
reset reclen to record length according to output
*/
logdump> reclen 1000
/*
position back to 100000
*/
logdump> pos 100000
logdump> n
/*
you will have output for whole record. The left Panel C1 variable is the SQL statement.
you can try it to find out the real error
*/
6. fixed the issue and restart the replicate
how to fix depends on errors and database settings and even data.
7. skip the transaction and restart the replicate
if you find out that the transaction can be skip.
find RBA position for next record using logdump
logdump> pos 100000
logdump> n
logdump> n
2013/09/24 23:15:09.334.111 DDLOP Len 115 RBA 101000
Name:
After Image: Partition 0 G s
2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'
ggsci> alter replicat test, extrba 101000
ggsci>info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED TEST 16:34:27 00:45:14
ggsci> start replicat test
ggsci> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TEST 16:34:27 00:45:14
reference:
http://blog.flimatech.com/2012/03/24/how-to-find-the-transaction-that-abended-oracle-goldengate/
http://satya-dba.blogspot.com/2012/02/ggsci-goldengate-command-interpreter.html
Tuesday, August 27, 2013
create directory for mapping drive in oracle database on windows
ISSUE:
data pump can not create files in directory created for mapping drive like S:\test_dir on windows.
CAUSE:
Mapping drive is specific for each account. The mapping drive like S:\test_dir must be recognized by the account running oracle service.
SOLUTION:
1. make sure the mapping drive you specified in " create directory statement" is available as the same mapping drive for the account running the oracle service. You can check services->properties -> logon to find the account name.
2. use direct network drive name like \\192.168.1.3\shearplace\testdir
SQL: create directory test_dir as '\\192.168.1.3\shearplace\testdir';
reference:
https://forums.oracle.com/message/9981090
data pump can not create files in directory created for mapping drive like S:\test_dir on windows.
CAUSE:
Mapping drive is specific for each account. The mapping drive like S:\test_dir must be recognized by the account running oracle service.
SOLUTION:
1. make sure the mapping drive you specified in " create directory statement" is available as the same mapping drive for the account running the oracle service. You can check services->properties -> logon to find the account name.
2. use direct network drive name like \\192.168.1.3\shearplace\testdir
SQL: create directory test_dir as '\\192.168.1.3\shearplace\testdir';
reference:
https://forums.oracle.com/message/9981090
Subscribe to:
Posts (Atom)