Michael Dinh
How To Delete Integrated Extract Replicat
Why is this important?
If processes are not unregistered from database, they are orphaned.
Hence:
unregister replicat $replicat_name DATABASE
unregister extract $extract_name DATABASE
$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved. GGSCI 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E_DB 00:00:05 00:00:00 EXTRACT RUNNING P_DB 00:00:00 00:00:00 REPLICAT RUNNING R_OWL 00:00:00 00:00:03 REPLICAT STOPPED R_JAY 00:00:00 00:24:15 GGSCI 2> info E_DB debug EXTRACT E_DB Last Started 2018-06-18 08:48 Status RUNNING Checkpoint Lag 00:00:05 (updated 00:00:00 ago) Process ID 365696 Log Read Checkpoint Oracle Integrated Redo Logs 2018-07-13 20:20:45 Seqno 151864, RBA 31183128 SCN 188.1068581841 (808522433489) GGSCI 3> info r_jay debug REPLICAT R_JAY Last Started 2018-06-16 11:42 Status STOPPED INTEGRATED Checkpoint Lag 00:00:00 (updated 578:24:33 ago) Log Read Checkpoint File ./dirdat/ 2018-06-19 17:55:08.604509 RBA 409140739 GGSCI 4> info r_owl debug REPLICAT R_OWL Last Started 2018-06-16 11:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Process ID 284878 Log Read Checkpoint File ./dirdat/ 2018-07-13 20:21:09.000628 RBA 132791008 GGSCI 5> exit SQL> @pr "select * from DBA_APPLY" APPLY_NAME : OGG$R_JAY QUEUE_NAME : OGGQ$R_JAY APPLY_CAPTURED : YES APPLY_USER : GGSUSER13 APPLY_TAG : 00 STATUS : DISABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 19-jun-2018 17:56:28 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Apply LCRID_VERSION : 2 ------------------------- APPLY_NAME : OGG$R_MIG - ORPHANED REPLICAT AS PROCESS DOES NOT EXISTS QUEUE_NAME : OGGQ$R_MIG QUEUE_OWNER : GGSUSER12 APPLY_CAPTURED : YES APPLY_USER : GGSUSER12 APPLY_TAG : 00 STATUS : DISABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 24-feb-2018 07:12:24 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Apply LCRID_VERSION : 2 ------------------------- APPLY_NAME : OGG$E_DB QUEUE_NAME : OGG$Q_E_DB QUEUE_OWNER : GGSUSER13 APPLY_CAPTURED : YES RULE_SET_NAME : RULESET$_9 APPLY_TAG : 00 STATUS : ENABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 18-jun-2018 08:48:02 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Capture LCRID_VERSION : 2 ------------------------- PL/SQL procedure successfully completed. SQL> @pr "select * from DBA_CAPTURE" CAPTURE_NAME : OGG$CAP_E_DB QUEUE_NAME : OGG$Q_E_DB START_SCN : 776572270090 STATUS : ENABLED CAPTURED_SCN : 808484653323 APPLIED_SCN : 808484649784 USE_DATABASE_LINK : NO FIRST_SCN : 776572270090 SOURCE_DATABASE : DB01 SOURCE_DBID : 689358028 SOURCE_RESETLOGS_SCN : 279476595350 SOURCE_RESETLOGS_TIME : 826720979 LOGMINER_ID : 1 MAX_CHECKPOINT_SCN : 808510343793 REQUIRED_CHECKPOINT_SCN : 808484621637 LOGFILE_ASSIGNMENT : IMPLICIT STATUS_CHANGE_TIME : 18-jun-2018 08:48:04 VERSION : 12.2.0.1.0 CAPTURE_TYPE : LOCAL CHECKPOINT_RETENTION_TIME : 7 PURPOSE : GoldenGate Capture SOURCE_ROOT_NAME : DB01 CLIENT_NAME : E_DB CLIENT_STATUS : ATTACHED OLDEST_SCN : 776572270090 FILTERED_SCN : 773378341423 ------------------------- PL/SQL procedure successfully completed.
Oracle Cloud Orchestration
Oracle Cloud has a pretty cool concept (Orchestration) to recreate an instance; however, it’s all hard coded.
The orchestration cannot be shared with some else to create the configuration with different name or from different account.
What version is the Orchestration and shouldn’t it be in the metadata.
{
"account" : "/Compute-601138841/default",
"description" : "",
"tags" : [ ],
"name" : "/Compute-601138841/me@yahoo.com/qs-classic",
"objects" : [ {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"managed" : true,
"description" : "qs-classic Storage Volume",
"bootable" : true,
"shared" : false,
"imagelist" : "/oracle/public/OL_7.2_UEKR4_x86_64",
"size" : "128G",
"properties" : [ "/oracle/public/storage/default" ],
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_storage"
},
"label" : "qs-classic_storage_1",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "StorageVolume",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/storage_1"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"description" : "qs-classic Security Rule (IP Network) Egress",
"tags" : [ "qs-classic" ],
"flowDirection" : "egress",
"acl" : "{{qs-classic_AccessControlList:name}}",
"enabledFlag" : true,
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_SecurityRule_Egress"
},
"label" : "qs-classic_SecurityRule_Egress",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "SecurityRule",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/f10f9f0d-9577-4f83-9b81-e1cc9d8bc9df"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"ipAddressPool" : "/oracle/public/public-ippool",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_IP_eth0_public"
},
"label" : "qs-classic_IP_eth0_public",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "IpAddressReservation",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/ac8dbfc5-d560-47e1-8d33-aeb54a0cc4c8"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"description" : "qs-classic Security Rule (IP Network)",
"tags" : [ "qs-classic" ],
"flowDirection" : "ingress",
"acl" : "{{qs-classic_AccessControlList:name}}",
"enabledFlag" : true,
"secProtocols" : [ "/oracle/public/ssh" ],
"dstVnicSet" : "{{qs-classic_VnicSet:name}}",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_SecurityRule"
},
"label" : "qs-classic_SecurityRule",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "SecurityRule",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/0ddfc441-7855-47a4-856f-15c400265975"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"appliedAcls" : [ "{{qs-classic_AccessControlList:name}}" ],
"description" : "qs-classic Virtual NIC Set",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_VnicSet",
"tags" : [ "qs-classic" ]
},
"label" : "qs-classic_VnicSet",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "VirtualNicSet",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/f7841713-54d1-4c9a-a6cb-32e3e84c753f"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"enabledFlag" : true,
"description" : "qs-classic Access Control List",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_AccessControlList",
"tags" : [ "qs-classic" ]
},
"label" : "qs-classic_AccessControlList",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "Acl",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/2f2915ca-5047-45bb-8875-0ae183a6425f"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"ipAddressPool" : "/oracle/public/cloud-ippool",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_IP_eth0_cloud"
},
"label" : "qs-classic_IP_eth0_cloud",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "IpAddressReservation",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/e3f78f72-3eca-4dd1-a054-fdd3fee8a51b"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : false,
"template" : {
"networking" : {
"eth0" : {
"vnic" : "/Compute-601138841/me@yahoo.com/qs-classic_eth0",
"ipnetwork" : "/Compute-601138841/default",
"is_default_gateway" : true,
"nat" : [ "network/v1/ipreservation:{{qs-classic_IP_eth0_public:name}}", "network/v1/ipreservation:{{qs-classic_IP_eth0_cloud:name}}" ],
"vnicsets" : [ "{{qs-classic_VnicSet:name}}" ]
}
},
"name" : "/Compute-601138841/me@yahoo.com/qs-classic",
"boot_order" : [ 1 ],
"storage_attachments" : [ {
"volume" : "{{qs-classic_storage_1:name}}",
"index" : 1
} ],
"label" : "qs-classic",
"shape" : "oc3",
"imagelist" : "/oracle/public/OL_7.2_UEKR4_x86_64",
"sshkeys" : [ "/Compute-601138841/me@yahoo.com/qs-classic" ]
},
"label" : "qs-classic_instance",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "Instance",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/instance"
} ],
"desired_state" : "active"
}
Using Postman For REST API on Oracle Cloud Infrastructure
Just completed quick training for Oracle Cloud Infrastructure (OCI)
REST API can be used on OCI for automation and presentation.
I find presentation for JSON results from CLI on server is UGLY.
Finally, I found POSTMAN which does to very nice job of presentation.
POSTMAN has results history, command history, search function.
https://www.getpostman.com/apps
Using GoldenGate LogDump To Find Bad Data
GoldenGate Primary Extract from source database captured data without any issues;
however, target was not able to consume the data since GoldenGate process would ABEND.
Unfortunately, I cannot provide all the details but high level.
Logdump 2433 >pos 0 +++ Starting with GoldenGate 12.2 TDR – Table Definition Record is in trail +++ This will provide metadata for the table Reading forward from RBA 0 Logdump 2434 >SCANFORMETADATA ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 86 (x0056) IO Time : 2018/03/25 18:24:23.307.797 IOType : 170 (xaa) OrigNode : 1 (x01) TransInd : . (x01) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) DDR/TDR Idx: (001, 000) AuditPos : 3277290592 Continued : N (x00) RecCount : 1 (x01) 2018/03/25 18:24:23.307.797 Metadata Len 86 RBA 1689 Name: * DDR Version: 1 Database type: ORACLE Character set ID: CESU-8 National character set ID: UTF-16 Locale: neutral Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14 TimeZone: GMT-05:00 Global name: DBNAME * Logdump 2435 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 561 (x0231) IO Time : 2018/03/25 18:28:16.317.879 IOType : 170 (xaa) OrigNode : 2 (x02) TransInd : . (x01) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) DDR/TDR Idx: (001, 001) AuditPos : 3352410292 Continued : N (x00) RecCount : 1 (x01) 2018/03/25 18:28:16.317.879 Metadata Len 561 RBA 1826 Name: SCHEMA.TABLE * 1)Name 2)Data Type 3)External Length 4)Fetch Offset 5)Scale 6)Level 7)Null 8)Bump if Odd 9)Internal Length 10)Binary Length 11)Table Length 12)Most Sig DT 13)Least Sig DT 14)High Precision 15)Low Precision 16)Elementary Item 17)Occurs 18)Key Column 19)Sub DataType 20)Native DataType 21)Character Set 22)Character Length 23)LOB Type 24)Partial Type * TDR version: 1 Definition for table SCHEMA.SCHEMA Record Length: 4298 Columns: 7 ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 2 -1 0 0 0 A B C D E F G H I J K L M N O P Q R Position 18)Key Column identify column is Primary Key; hence, ID a primary key column. I labeled each column using the alphabet for reference and R is the 18th letter of the alphabet. Even though ID is the first column of the table, GoldenGate offset starts with 0 SQL> desc SCHEMA.TABLE Name Null? Type ----------------------------------------- -------- ---------------------------- 0 ID NOT NULL NUMBER 5 TEXT VARCHAR2(4000) SQL> r 1 select b.column_name,a.constraint_type 2 from dba_constraints a, dba_cons_columns b 3 where a.table_name = b.table_name 4 and a.constraint_name=b.constraint_name 5 and a.constraint_type = 'P' 6 and a.table_name='TABLE' 7 and a.owner='SCHEMA' 8* COLUMN_NAME C ------------------------------ - ID P Logdump 2500 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x0c) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 885 (x0375) IO Time : 2018/05/10 14:16:43.000.514 IOType : 15 (x0f) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 36261 AuditPos : 2145515112 Continued : N (x00) RecCount : 1 (x01) 2018/05/10 14:16:43.000.514 FieldComp Len 885 RBA 88598006 Name: SCHEMA.TABLE (TDR Index: 1) After Image: .................... 0000 000c 0000 0008 3239 3533 3432 3936 0001 0009 | ........29534296.... 0000 0005 3937 3932 3600 0200 0500 0000 0131 0003 | .................... 0005 0000 0001 3600 0400 0b00 0000 0731 3738 3337 | .................... 3939 0005 0325 0000 0321 456e 6a6f 7920 796f 7572 | .................... 2073 7461 7920 696e 206f 7572 2068 6f74 656c 7320 | .................... 7769 7468 2074 6865 206d 6f73 7420 6469 7363 6f75 | .................... 6e74 6564 2072 6174 | .................... Column 0 (x0000), Len 12 (x000c) 0000 0008 3239 3533 3432 3936 | ....29534296 --- PRIMARY KEY VALUE Column 1 (x0001), Len 9 (x0009) 0000 0005 3937 3932 36 | ....97926 Column 2 (x0002), Len 5 (x0005) 0000 0001 31 | ....1 Column 3 (x0003), Len 5 (x0005) 0000 0001 36 | ....6 SQL> select id, substr(TEXT,1,30) txt, vsize(TEXT), length(TEXT) from SCHEMA.TABLE where ID in (29534296); ID LTXT VSIZE(LTEXT) LENGTH(LTEXT) ---------- ------------------------------ ------------ ------------- 29534296 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 801 800 SQL> For database, NLS_LENGTH_SEMANTICS=BYTE; hence, 800 characters should equal 800 bytes. However, one character is most likely a multi-byte character which target cannot consume as it is not able to handle multi-byte.
Goldengate REPORTING P2
Previous post for Goldengate REPORTING
Goldengate reporting has to be the least implemented functionality until it is needed and often in hindsight.
Here is an example of what I would normally implement.
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 15 MINUTES, RATE
DISCARDROLLOVER AT 00:01 ON SUNDAY
If business only cares about monthly data, then would not make sense to collect daily nor would it make sense for business to request such.
DataGuard Convention
Good convention and implementation make life and automation so much simpler and more time for golfing.
I have seen some really poor and really good implementation and here’s a good one.
Wish I can take credit for it and unfortunately I cannot.
The scripts were created by whoa.
Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.
Use ORACLE_UNQNAME for DataGuard Environment
==================================================================================================== +++ PRIMARY RACONENODE ==================================================================================================== SQL> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string test SQL> $ sysresv|tail -1 Oracle Instance alive for sid "test_1" $ env|grep ORACLE ORACLE_SID=test_1 (db_name) ORACLE_UNQNAME=test (db_unique_name) $ srvctl config database -d $ORACLE_UNQNAME Database unique name: test Database name: test Oracle home: /u01/app/oracle/product/11g/db_1 Oracle user: oracle Spfile: +FLASH/test/spfiletest.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: test Database instances: Disk Groups: FLASH,DATA Mount point paths: Services: testsvc Type: RACOneNode Online relocation timeout: 30 Instance name prefix: test Candidate servers: host01,host02 Database is administrator managed ==================================================================================================== +++ STANDBY NON-RAC ==================================================================================================== SQL> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string testdr SQL> $ sysresv|tail -1 Oracle Instance alive for sid "test" $ env|grep ORACLE ORACLE_SID=test (db_name) ORACLE_UNQNAME=testdr (db_unique_name) $ srvctl config database -d $ORACLE_UNQNAME Database unique name: testdr Database name: test Oracle home: /u01/app/oracle/product/11g/db_1 Oracle user: oracle Spfile: Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Database instance: test Disk Groups: DATA,FLASH Services: ==================================================================================================== DATAGUARD BROKER CONFIGURATION ==================================================================================================== DGMGRL> show configuration Configuration - dg_test (db_name) Protection Mode: MaxPerformance Databases: test - Primary database (db_unique_name) testdr - Physical standby database (db_unique_name) Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database test Database - test Role: PRIMARY Intended State: TRANSPORT-OFF Instance(s): test_1 test_2 Database Status: SUCCESS DGMGRL> show database testdr Database - testdr Role: PHYSICAL STANDBY Intended State: APPLY-OFF Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 7 seconds (computed 0 seconds ago) Apply Rate: (unknown) Real Time Query: OFF Instance(s): test Database Status: SUCCESS DGMGRL> exit ==================================================================================================== ls -l dg*.sh ==================================================================================================== -rwxr-xr-x 1 oracle dba 377 May 08 21:50 dg_lag.sh -rwxr-x--- 1 oracle dba 445 May 08 20:12 dg_start.sh -rwxr-xr-x 1 oracle dba 337 May 08 20:05 dg_status.sh -rwxr-x--- 1 oracle dba 447 May 08 20:12 dg_stop.sh ==================================================================================================== dg_lag.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / show database ${ORACLE_SID} SendQEntries show database ${ORACLE_UNQNAME} RecvQEntries show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== cat dg_start.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / edit database ${ORACLE_SID} set state='TRANSPORT-ON'; edit database ${ORACLE_UNQNAME} set state='APPLY-ON'; show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== dg_status.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== dg_stop.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / edit database ${ORACLE_SID} set state='TRANSPORT-OFF'; edit database ${ORACLE_UNQNAME} set state='APPLY-OFF'; show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } check_dg . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit
DBFS Nightmare
==================================================================================================== How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1) ==================================================================================================== High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure: ---------------------------------------------------------------------------------------------------- 1) Create a NEW tablespace 2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace. 3) Run dbms_dbfs_sfs.reorganizeFS -->> EXEC DBMS_DBFS_SFS.REORGANIZEFS(SRCSTORE=>'FS_FS1', DSTSTORE=>'FS_TMP_FS'); 4) The dbfs data is now in the smaller NEW tablespace. 5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem 6) The OLD original tablespace is empty now. ---------------------------------------------------------------------------------------------------- To reuse the same tablespace again please follow the below steps ---------------------------------------------------------------------------------------------------- 7) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the OLD ORIGINAL tablespace. 8) Run dbms_dbfs_sfs.reorganizeFS 9) The dbfs data is now in the smaller the ORIGINAL tablespace. 10) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem 11) The NEW small tablespace is empty now and can be dropped. ==================================================================================================== DBFS tablespace keep growing not using expired blocks (Doc ID 2327299.1) ==================================================================================================== ---------------------------------------------------------------------------------------------------- First perform the DBFS re-org to cleanup the tablespace after removing the files at file system level by following below document ---------------------------------------------------------------------------------------------------- How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1) ---------------------------------------------------------------------------------------------------- After the re-org, set the below parameter to reuse the expired blocks ---------------------------------------------------------------------------------------------------- ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0; ==================================================================================================== ORA-1654 - DBFS FREE SPACE NOT RECLAIMED AFTER CLEARING FILES (Doc ID 1948305.1) ==================================================================================================== ---------------------------------------------------------------------------------------------------- a) Check Lob retention setting: ---------------------------------------------------------------------------------------------------- ex. SQL> select table_name,retention_type,retention_value,retention,securefile from dba_lobs where table_name = 'T_FS1'; ---------------------------------------------------------------------------------------------------- b) Change LOB retention to none. ---------------------------------------------------------------------------------------------------- ex. SQL> alter table DBFS_USER.T_FS1 modify lob (FILEDATA) (retention none);
After countless discussions, team found GOLD.
12.1 Shrinking and Reorganizing DBFS Filesystems STEPS:sqlplus /as sysdba
create bigfile tablespace DBFS_NEW datafile size 8G autoextend on next 1G maxsize 70G;
grant dba to dbfs_user;
alter user dbfs_user default role all;
exit
+++ REORGANIZE DBFS FILESYSTEM FS1 IN TABLESPACE DBFS_TS INTO A NEW TABLESPACE DBFS_NEW,
+++ USING A TEMPORARY FILESYSTEM NAMED TMP_FS, WHERE ALL FILESYSTEMS BELONG TO DATABASE USER DBFS_USER
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user
@dbfs_create_filesystem DBFS_NEW TMP_FS
exec dbms_dbfs_sfs.reorganizefs('FS1','TMP_FS');
@dbfs_drop_filesystem TMP_FS
purge user_recyclebin;
exit
sqlplus / aa sysdba
revoke dba from dbfs_user;
select count(*) from dba_extents where tablespace_name='DBFS_TS';
select count(*) from dba_extents where tablespace_name='DBFS_NEW';
-- DROP TABLESPACE HAVING ZERO EXTENTS
-- BE CAREFUL IN CASE USER AND DBFS ARE USING SAME TABLESPACE
Example:
create user dbfs_user identified by **** default tablespace dbfs_ts quota unlimited on dbfs_ts;
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1
TRUE CONFESSION:
I created an imperfect plan at first that might have saved me from a major catastrophe.
Create DBFS_TMP in a separate Disk Group since I was concern existing may not have enough storage.
Reorg from DBFS_TS to DBFS_TMP (bad idea to name tablespace as DBFS_TMP as LOB Segments migrated here).
Reorg from DBFS_TMP back to DBFS_TS.
Drop tablespace DBFS_TMP.
There’s 29G difference between old and new. How much if this space will be reclaimable due to HWM in data file?
Performing reorg twice is really not an option in prod.
RESULTS:SEGMENT_NAME TABLESPACE_NAME MB EXTENTS ------------------------------ ------------------------------ ---------- ---------- LOB_SFS$_FST_1 DBFS_TS 30647.1875 1851 LOB_SFS$_FST_6225924 DBFS_NEW 1025.125 130WARNINGS:
This has only been tested in a vacuum (non-prod env w very low activities) – YMMV.
Multiplex Redo Log
When db_create_online_log_dest_1 is defined, REDO log is not multiplexed which is good for creating STANDBY REDO.
REDO log is created at db_create_online_log_dest_1 ONLY.
However, when creating ONLINE REDO, db_create_online_log_dest_1 should NOT be defined to be multiplexed.
REDO logs are created at db_create_file_dest and db_recovery_file_dest.
[oracle@db-asm-1 sql]$ sqlplus / as sysdba @ logfile.sql SQL*Plus: Release 12.2.0.1.0 Production on Thu May 3 05:56:30 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 05:56:30 SYS @ owl:>show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA db_create_online_log_dest_1 +FRA db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 05:56:30 SYS @ owl:>show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 7116M 05:56:30 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc; GROUP# MEMBER TYPE STATUS ---------- -------------------------------------------------------------------------------- ------- ------- 1 +DATA/OWL/ONLINELOG/group_1.261.962643743 ONLINE 1 +FRA/OWL/ONLINELOG/group_1.257.962643743 2 +DATA/OWL/ONLINELOG/group_2.262.962643743 2 +FRA/OWL/ONLINELOG/group_2.258.962643743 3 +DATA/OWL/ONLINELOG/group_3.263.962643745 3 +FRA/OWL/ONLINELOG/group_3.259.962643745 6 rows selected. 05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------------- 1 1 79 104857600 CURRENT 2 1 77 104857600 INACTIVE 3 1 78 104857600 INACTIVE 05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2; no rows selected 05:56:30 SYS @ owl:>alter database add standby logfile thread 1 group 11 size 104857600; Database altered. 05:57:03 SYS @ owl:>alter system set db_create_online_log_dest_1=''; System altered. 05:57:39 SYS @ owl:>alter database add logfile thread 1 group 4 size 104857600; Database altered. 05:58:01 SYS @ owl:>@logfile.sql 05:58:06 SYS @ owl:>set lines 200 tab off trimsp on pages 1000 05:58:06 SYS @ owl:>col member for a80 05:58:06 SYS @ owl:>break on TYPE 05:58:06 SYS @ owl:>set echo on 05:58:06 SYS @ owl:>show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA db_create_online_log_dest_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 05:58:06 SYS @ owl:>show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 7116M 05:58:06 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc; GROUP# MEMBER TYPE STATUS ---------- -------------------------------------------------------------------------------- ------- ------- 1 +DATA/OWL/ONLINELOG/group_1.261.962643743 ONLINE 1 +FRA/OWL/ONLINELOG/group_1.257.962643743 2 +DATA/OWL/ONLINELOG/group_2.262.962643743 2 +FRA/OWL/ONLINELOG/group_2.258.962643743 3 +DATA/OWL/ONLINELOG/group_3.263.962643745 3 +FRA/OWL/ONLINELOG/group_3.259.962643745 4 +DATA/OWL/ONLINELOG/group_4.267.975131881 4 +FRA/OWL/ONLINELOG/group_4.295.975131881 11 +FRA/OWL/ONLINELOG/group_11.296.975131823 STANDBY 9 rows selected. 05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------------- 1 1 79 104857600 CURRENT 2 1 77 104857600 INACTIVE 3 1 78 104857600 INACTIVE 4 1 0 104857600 UNUSED 05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------- 11 1 0 104857600 UNASSIGNED 05:58:07 SYS @ owl:>
Who’s Gathering DB Stats?
There was an incident where statistics were being gathered during prime operating hours causing performance issues. One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours. Speculation is stats are being gathered manually and how to prove this? AUTO JOB has OPERATION : gather_database_stats (auto). MANUAL JOB is not being run by scheduler either; otherwise, there would be JOB_NAME. Half of the mystery is solve, but where is gather_table_stats running from? Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> @pr "SELECT ID,OPERATION,START_TIME,END_TIME,STATUS,JOB_NAME,SESSION_ID FROM dba_optstat_operations where START_TIME>trunc(sysdate-1) ORDER BY start_time DESC" ID : 6939 OPERATION : gather_table_stats START_TIME : 26-APR-18 05.00.12.387231 PM -07:00 END_TIME : 26-APR-18 05.00.21.509607 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6918 OPERATION : export_stats_for_dp START_TIME : 26-APR-18 03.47.09.574643 PM -07:00 END_TIME : 26-APR-18 03.47.25.336241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 201 ------------------------- ID : 6917 OPERATION : gather_table_stats START_TIME : 26-APR-18 03.10.16.126374 PM -07:00 END_TIME : 26-APR-18 04.59.51.410241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6916 OPERATION : gather_table_stats START_TIME : 26-APR-18 02.09.44.123132 PM -07:00 END_TIME : 26-APR-18 02.09.45.695904 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6915 OPERATION : gather_table_stats START_TIME : 26-APR-18 12.57.11.352671 PM -07:00 END_TIME : 26-APR-18 02.09.43.579331 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6922 OPERATION : restore_table_stats START_TIME : 26-APR-18 02.00.00.949528 AM -07:00 END_TIME : 26-APR-18 02.00.01.297300 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 877 ------------------------- ID : 6914 OPERATION : gather_table_stats START_TIME : 25-APR-18 11.57.35.764007 PM -07:00 END_TIME : 26-APR-18 12.05.11.086928 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 870 ------------------------- ID : 6921 OPERATION : gather_database_stats (auto) START_TIME : 25-APR-18 10.00.06.197933 PM -07:00 END_TIME : 26-APR-18 02.00.01.621582 AM -07:00 STATUS : TIMED OUT JOB_NAME : ORA$AT_OS_OPT_SY_16648 SESSION_ID : 877 ------------------------- ID : 6913 OPERATION : gather_table_stats START_TIME : 25-APR-18 02.26.33.270421 PM -07:00 END_TIME : 25-APR-18 02.26.42.759697 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 1921 ------------------------- ID : 6912 OPERATION : gather_table_stats START_TIME : 25-APR-18 12.20.35.728909 PM -07:00 END_TIME : 25-APR-18 02.26.18.996076 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6911 OPERATION : gather_table_stats START_TIME : 25-APR-18 10.13.45.127514 AM -07:00 END_TIME : 25-APR-18 10.13.46.570807 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6910 OPERATION : gather_table_stats START_TIME : 25-APR-18 08.57.44.914619 AM -07:00 END_TIME : 25-APR-18 10.13.44.623245 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6920 OPERATION : restore_table_stats START_TIME : 25-APR-18 02.00.00.220393 AM -07:00 END_TIME : 25-APR-18 02.00.00.490261 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 208 ------------------------- PL/SQL procedure successfully completed. SQL>
Skip Goldengate Replicat Transaction
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.15 17640173 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131101.0605.2_FBO Linux, x64, 64bit (optimized), Oracle 11g on Nov 19 2013 03:18:45 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
==================================================================================================== ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE" WHERE "JOENUMMER" = :b0. ==================================================================================================== +++ SKIPTRANSACTION GGSCI> start replicat REP1 SKIPTRANSACTION +++ REVIEW PRM [gguser]$ grep -i discard rep1.prm --REPERROR (DEFAULT, DISCARD) REPERROR (-1, DISCARD) REPERROR (2291, DISCARD) DISCARDFILE ./discard/rep1.discard append, MEGABYTES 1024 DISCARDROLLOVER AT 00:01 [gguser]$ +++ REVIEW SKIPPING FROM DISCARD [gguser]$ grep -c "Skipping delete from OWNER.T_JOE" rep1.discard 15276 [gguser]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|head Skipping delete from OWNER.T_JOE at seqno 4475 rba 87850906 * JOENUMMER = 1 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851339 * JOENUMMER = 2 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851735 * [gguser@viz-cp-dc1-p11 oracle]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|tail * JOENUMMER = 50093291 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033367 * JOENUMMER = 50094681 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 * JOENUMMER = 50094741 +++ REVIEW RBA FROM DISCARD [gguser]$ grep rba rep1.discard|head -1 Aborting transaction on ./dirdat/nd beginning at seqno 4475 rba 87850906 [gguser]$ grep rba rep1.discard|tail -1 Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 [gguser]$ +++ NOTICE MATCH WITH LOGDUMP Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ==================================================================================================== GATHER DATA ====================================================================================================
GGATE@SQL> r
1 select count(*) from
2 (
3 (select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@dblink)
4 union all
5 (select JOENUMMER from OWNER.T_JOE@dblink minus select JOENUMMER from OWNER.T_JOE)
6 )
7*
COUNT(*)
———-
15273
GGATE@SQL>
+++ CREATE TEMPORARY TABLE
GGATE@SQL> create table T_JOE_DEL as select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@dblink;
+++ REVIEW DATA FROM TEMPORARY TABLE TO COMPARE WITH DISCARD
GGATE@SQL> r
1 select * from (
2 select JOENUMMER from T_JOE_DEL order by 1 asc
3* ) where rownum <11
JOE
————
1
2
3
21
23
24
25
26
27
28
10 rows selected.
GGATE@SQL>
GGATE@SQL> r
1 select * from (
2 select JOE from T_JOE_DEL order by 1 desc
3* ) where rownum <11
JOE
————
50094741
50094681
50093291
50093221
50093191
50093101
50092851
50092791
50092781
50092741
10 rows selected.
GGATE@SQL>
==================================================================================================== CORRECT DATA ==================================================================================================== GGATE@SQL> delete from OWNER.T_JOE where JOENUMMER in (select JOENUMMER from T_JOE_DEL); 15273 rows deleted. GGATE@SQL> commit; Commit complete. ==================================================================================================== VERIFY ROW COUNT ==================================================================================================== +++ USING COUNT MAY NOT BE THE BEST OPTION. GGATE@SQL> select count(*) from OWNER.T_JOE; COUNT(*) ---------- 9939 GGATE@SQL> select count(*) from OWNER.T_JOE@dblink; COUNT(*) ---------- 9939 GGATE@SQL> ==================================================================================================== REVIEW REPORT FILE ==================================================================================================== [gguser]$ grep SKIPTRANSACTION REP1*.rpt rep1.rpt:2018-04-17 12:15:15 INFO OGG-01370 User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ grep -i skip ggserr.log 2018-04-17 12:15:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): start replicat rep1 SKIPTRANSACTION. 2018-04-17 12:15:14 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.232.135.44:33310 (START REPLICAT rep1 SKIPTRANSACTION). 2018-04-17 12:15:15 INFO OGG-01370 Oracle GoldenGate Delivery for Oracle, rep1.prm: User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ ==================================================================================================== LOGDUMP TO FIND END OF TRANSACTONS ==================================================================================================== Logdump 15 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 16 >detail on Logdump 17 >fileheader detail Logdump 18 >ghdr on Logdump 19 >detail data Logdump 20 >ggstoken detail Logdump 21 >pos 87850906 Reading forward from RBA 87850906 Logdump 22 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 310 (x0136) IO Time : 2018/04/17 10:47:16.475.512 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 779280 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.475.512 Delete Len 310 RBA 87850906 Name: OWNER.T_JOE Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5441 4141 6264 7141 4159 0001 | AAAHkUAATAAAbdqAAY.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3135 | 7283708415 TokenID x36 '6' TRANID Info x00 Length 13 3232 2e33 302e 3139 3233 3539 39 | 22.30.1923599 Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 331 (x014b) IO Time : 2018/04/17 10:47:16.429.234 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13903264 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.429.234 Delete Len 331 RBA 94033767 Name: OWNER.T_JOE Before Image: Partition 4 G e GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5741 4141 4e6c 6c41 4177 0001 | AAAHkUAAWAAANllAAw.. Logdump 24 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 174 (x00ae) IO Time : 2018/04/17 10:47:24.429.491 IOType : 15 (x0f) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13947088 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:24.429.491 FieldComp Len 174 RBA 94034190 Name: OWNER.NEW_DATA Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6a59 4141 5441 4142 794b 4541 412f 0001 | AAAHjYAATAAByKEAA/.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3538 | 7283708458 TokenID x36 '6' TRANID Info x00 Length 13 3132 2e31 362e 3330 3031 3139 37 | 12.16.3001197 Logdump 25 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 26 >count LogTrail ./dirdat/nd004475 has 92822 records Total Data Bytes 92730182 Avg Bytes/Record 999 Delete 20937 Insert 5405 FieldComp 724 LargeObject 65755 Others 1 Before Images 21163 After Images 71658 Average of 1589 Transactions Bytes/Trans ..... 61161 Records/Trans ... 58 Files/Trans ..... 5 Logdump 27 >detail on Logdump 28 >filter inc filename OWNER.T_JOE Logdump 29 >count Scanned 10000 records, RBA 12734577, 2018/04/17 07:25:42.524.558 Scanned 20000 records, RBA 25670230, 2018/04/17 08:00:11.480.213 Scanned 30000 records, RBA 38698934, 2018/04/17 08:30:24.488.669 Scanned 40000 records, RBA 51436567, 2018/04/17 08:59:11.452.549 Scanned 50000 records, RBA 63868041, 2018/04/17 09:43:10.477.605 Scanned 60000 records, RBA 76010927, 2018/04/17 10:14:59.472.122 Scanned 70000 records, RBA 94264594, 2018/04/17 10:47:31.447.436 LogTrail ./dirdat/nd004475 has 15296 records Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Filtering matched 15296 records suppressed 77526 records Average of 2 Transactions Bytes/Trans ..... 2745786 Records/Trans ... 7648 Files/Trans ..... 110 OWNER.T_JOE Partition 4 Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Logdump 30 >
Framework To Run SQL For All Active DB Instances
Requirement is to configure hugepages for multiple RAC database instances.
pmon processes
grid 12692 1 0 09:39 ? 00:00:00 asm_pmon_+ASM1 grid 13296 1 0 09:39 ? 00:00:00 mdb_pmon_-MGMTDB oracle 13849 1 0 09:40 ? 00:00:00 ora_pmon_DEV1 oracle 13851 1 0 09:40 ? 00:00:00 ora_pmon_QA1 oracle 13854 1 0 09:40 ? 00:00:00 ora_pmon_PERF1 oracle 13855 1 0 09:40 ? 00:00:00 ora_pmon_TEST1 oracle 14998 1 0 09:40 ? 00:00:00 ora_pmon_INT1
Create parameter.sh which will run parameter.sql.
You might be thinking, WTH is this person thinking!
I wanted SQL script to be reusable.
Run parameter.sql
oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba @ parameter.sql
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:25:56 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database TRUE 1 0 FALSE FALSE
TRUE 2 0 FALSE FALSE
cluster_database_instances 2 1 0 4294967295 TRUE
2 2 0 4294967295 TRUE
db_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
db_name hawk 1 0 NULL FALSE
hawk 2 0 NULL FALSE
db_unique_name hawk 1 0 NONE TRUE
hawk 2 0 NONE TRUE
instance_groups 1 0 NULL TRUE
2 0 NULL TRUE
instance_name hawk1 1 0 NULL TRUE
hawk2 2 0 NULL TRUE
instance_number 1 1 0 0 FALSE
2 2 0 0 FALSE
instance_type RDBMS 1 0 NONE TRUE
RDBMS 2 0 NONE TRUE
memory_max_target 0 1 0 0 TRUE
0 2 0 0 TRUE
memory_target 0 1 0 0 TRUE
0 2 0 0 TRUE
pdb_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
pga_aggregate_limit 2G 1 0 1 TRUE
2G 2 0 1 TRUE
pga_aggregate_target 256M 1 0 0 FALSE
256M 2 0 0 FALSE
sga_max_size 768M 1 0 1000 TRUE
768M 2 0 1000 TRUE
sga_target 768M 1 0 0 FALSE
768M 2 0 0 FALSE
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
34 rows selected.
13:25:56 SYS @ hawk1:>
Run parameter.sh
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$ ~/parameter.sh
******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------
******** SQL Script: /home/oracle/parameter.sql
The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:26:55 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database TRUE 1 0 FALSE FALSE
TRUE 2 0 FALSE FALSE
cluster_database_instances 2 1 0 4294967295 TRUE
2 2 0 4294967295 TRUE
db_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
db_name hawk 1 0 NULL FALSE
hawk 2 0 NULL FALSE
db_unique_name hawk 1 0 NONE TRUE
hawk 2 0 NONE TRUE
instance_groups 1 0 NULL TRUE
2 0 NULL TRUE
instance_name hawk1 1 0 NULL TRUE
hawk2 2 0 NULL TRUE
instance_number 1 1 0 0 FALSE
2 2 0 0 FALSE
instance_type RDBMS 1 0 NONE TRUE
RDBMS 2 0 NONE TRUE
memory_max_target 0 1 0 0 TRUE
0 2 0 0 TRUE
memory_target 0 1 0 0 TRUE
0 2 0 0 TRUE
pdb_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
pga_aggregate_limit 2G 1 0 1 TRUE
2G 2 0 1 TRUE
pga_aggregate_target 256M 1 0 0 FALSE
256M 2 0 0 FALSE
sga_max_size 768M 1 0 1000 TRUE
768M 2 0 1000 TRUE
sga_target 768M 1 0 0 FALSE
768M 2 0 0 FALSE
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
34 rows selected.
13:26:55 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$
Requirements are .sh and .sql must have the same name and reside in the same location.
.sh can be called from any location.
parameter.sh
#!/bin/sh
# --------------------------------------------------------------------------------
# parameter.sh
# MDinh April 12, 2018
#
# Shell script will run SQL script having the same base name
# for all active database instances.
# --------------------------------------------------------------------------------
DN=`dirname $0`
BN=`basename $0`
SQL_SCRIPT_DIR=$DN
SQL=`echo $BN|cut -d'.' -f1`.sql
echo
echo "******** Current ora_pmon:"
echo "----------------------------------------"
ps -eo cmd|grep ora_pmon|grep -v grep
echo "----------------------------------------"
echo
echo "******** SQL Script: "$SQL_SCRIPT_DIR/$SQL
echo
for x in `ps -eo cmd|grep ora_pmon|grep -v grep|awk -F "_" '{print $NF}'`
do
ORAENV_ASK=NO
set -a
ORACLE_SID=$x
. oraenv
set +a
sysresv|tail -1
sqlplus -L "/ as sysdba" << EOF
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
start $SQL_SCRIPT_DIR/$SQL
exit
EOF
if [ "$?" != "0" ]; then
echo "$ORACLE_SID ERROR: Running $SQL_SCRIPT_DIR/$SQL"
exit 1
fi
done
exit
parameter.sql
col name for a30
col value for a30
col default_value for a13
col display_value for a13
col inst_id for 99
break on name
set lines 200 pages 1000 trimsp on tab off
select name,CDB from v$database
;
select name,display_value,inst_id,con_id,default_value,isdefault
from gv$parameter
where regexp_like (name,'^sga|^pga|^memory|^cluster.*database|^instance|use_large_pages|db.*name','i')
order by name,value,inst_id
;
Frame work for shell script is the same. Just make a copy and update any comments.
oracle@racnode-dc1-1:hawk1:/home/oracle
$ ll
total 36
-rwxr-xr-x 1 oracle oinstall 19 Feb 10 20:44 db.env
-rwxr-xr-x 1 oracle oinstall 49 Feb 10 20:45 gi.env
-rwxr-xr-x 1 oracle oinstall 1020 Apr 14 13:24 parameter.sh
-rw-r--r-- 1 oracle oinstall 414 Apr 14 13:24 parameter.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_only.sh
-rw-r--r-- 1 oracle oinstall 430 Apr 12 17:50 set_db_use_large_pages_only.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_true.sh
-rw-r--r-- 1 oracle oinstall 430 Apr 12 17:53 set_db_use_large_pages_true.sql
-rw-r--r-- 1 oracle oinstall 1909 Jan 29 02:39 wc.sql
oracle@racnode-dc1-1:hawk1:/home/oracle
$ diff parameter.sh set_db_use_large_pages_true.sh
3c3
# set_db_use_large_pages_true.sh
oracle@racnode-dc1-1:hawk1:/home/oracle
$ ./set_db_use_large_pages_true.sh
******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------
******** SQL Script: ./set_db_use_large_pages_true.sql
The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:48:17 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
13:48:17 SYS @ hawk1:>alter system set USE_LARGE_PAGES=TRUE scope=spfile sid='*'
13:48:17 2 ;
System altered.
13:48:17 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/home/oracle
$
Check 12.1.0.2 Alert Log For HugePages Usage
What! Another post on hugepages – seriously?
+ grep 'Dump of system resources acquired for SHARED GLOBAL AREA' -B1 -A22 database alert log + tail -25 2018-04-13T09:40:23.908633-07:00 Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 2018-04-13T09:40:23.916573-07:00 Per process system memlock (soft) limit = UNLIMITED 2018-04-13T09:40:23.920591-07:00 Expected per process system memlock (soft) limit to lock SHARED GLOBAL AREA (SGA) into memory: 2996M 2018-04-13T09:40:23.928517-07:00 Available system pagesizes: 4K, 2048K 2018-04-13T09:40:23.936717-07:00 Supported system pagesize(s): 2018-04-13T09:40:23.943044-07:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 2018-04-13T09:40:23.947112-07:00 2048K 2303 1498 1498 NONE 2018-04-13T09:40:23.951899-07:00 Reason for not supporting certain system pagesizes: 2018-04-13T09:40:23.960107-07:00 4K - Large pagesizes only 2018-04-13T09:40:23.965247-07:00 ==================================================================================================== Tue Apr 10 12:29:13 2018 Dump of system resources acquired for SHARED GLOBAL AREA (SGA) Tue Apr 10 12:29:13 2018 Per process system memlock (soft) limit = 128G Tue Apr 10 12:29:13 2018 Expected per process system memlock (soft) limit to lock SHARED GLOBAL AREA (SGA) into memory: 4002M Tue Apr 10 12:29:13 2018 Available system pagesizes: 4K, 2048K Tue Apr 10 12:29:13 2018 Supported system pagesize(s): Tue Apr 10 12:29:13 2018 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) Tue Apr 10 12:29:13 2018 4K Configured 5 1024005 NONE Tue Apr 10 12:29:13 2018 2048K 0 2001 0 NONE Tue Apr 10 12:29:13 2018 RECOMMENDATION: Tue Apr 10 12:29:13 2018 1. For optimal performance, configure system with expected number of pages for every supported system pagesize prior to the next
Oracle Different Levels of Hell
Did not know there exists many levels of hell and Oracle certainly has them.
Would it be bad if someone is searching for hell and this blog is listed as top 10? :=(
Here’s the effort one needs to go through to determine what patch to apply for Goldengate software (binary) as part of quarterly Critical Patch Updates.
Hint: Goldengate does not participate in quarterly patch updates.
Find the latest patch available and patch using Opatch or install newer database compatible version using runInstaller.
Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)
Latest RDBMS version Oracle Server 12.1.0.2
Let’s create a new doc for new version but don’t provide reference to it. Make the user find it.
Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations (Doc ID 2193391.1)
Latest OGG Release 12.3
Latest RDBMS Release 12.2.0.1
OGG Release/OGG Patch
12.3/Recommended 12.3.0.1.2 or higher *** This is not a patch
12.2/Recommended 12.2.0.2.0 or higher *** This is not a patch
12.2/Minimum 12.2.0.1.170221 *** This is a patch
Master Note for Oracle GoldenGate Core Product Patch Sets (Doc ID 1645495.1)
There are no patch sets available for 12.3.0.1 at this time.
Latest OGG v12.2.0.1 Patch Set Availability Notes
Good But Not Good Enough Coding Practice
Good: Alert from from localhost but what script is this? If there was only 1 cron entry and intuitive, then I would not be blogging about it.
ALERT … Goldengate process “EXTRACT(PU)” has a lag of 02 hour 22 min on localhost
Better: Alert is from localhost for monitoring_gg.sh
monitoring_gg.sh ALERT … Goldengate process “EXTRACT(PU)” has a lag of 00 hour 00 min on localhost
That is all.
Inconsistencies – Are they good or bad?
Different companies will have different implementations of the same, e.g. backup, naming convention, to name a few.
However, should there be different implementations of the same in the company itself?
There are pros and cons to everything and there are no wrongs or rights, just objectives and requirements.
Example: if you wants to have the same experience with coffee no matter where, you goes to Starbucks.
Don’t let it frustrate you. Think of it as freedom and the opportunity to do thing the way you want to.
Is the glass half empty or half full? For me, it’s half empty because I can fill it up with what I want.
Upgrade 12.2 Journey – DataGuard
Oracle Data Guard Broker Changes in Oracle Database 12c Release 2 (12.2.0.1)
How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)
Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby
How Do You Create Data Guard Configuration?
I have taken for granted to create Data Guard Configuration the same way most of the time that I don’t know what goes wrong with done differently.
oracle@racnode-dc1-1:hawk1:/home/oracle $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 23:41:49 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options 23:41:49 SYS @ hawk1:>show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string hawk db_unique_name string hawk pdb_file_name_convert string 23:42:04 SYS @ hawk1:>alter system set dg_broker_start=true sid='*' scope=memory; System altered. 23:42:40 SYS @ hawk1:> +++ CREATE CONFIGURATION USING UPPER CASE WITHOUT QUOTES oracle@racnode-dc1-1:hawk1:/home/oracle $ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. --- NO QUOTES USE AND ALL UPPERCASE - EASIEST METHOD --- Broker convert database to match that of db_unique_name DGMGRL> CREATE CONFIGURATION DG_CONFIG AS PRIMARY DATABASE IS HAWK CONNECT IDENTIFIER IS HAWK; Configuration "dg_config" created with primary database "hawk" DGMGRL> show configuration Configuration - dg_config Protection Mode: MaxPerformance Members: hawk - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> show database hawk Database - hawk Role: PRIMARY Intended State: OFFLINE Instance(s): hawk1 hawk2 Database Status: DISABLED DGMGRL> remove configuration Removed configuration --- CONFIGURATION IS UPPERCASE --- Does it look better in uppercase? DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'hawk' CONNECT IDENTIFIER IS HAWK; Configuration "DG_CONFIG" created with primary database "hawk" DGMGRL> show configuration Configuration - DG_CONFIG Protection Mode: MaxPerformance Members: hawk - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> show database hawk Database - hawk Role: PRIMARY Intended State: OFFLINE Instance(s): hawk1 hawk2 Database Status: DISABLED DGMGRL> remove configuration Removed configuration +++ ISSUES OCCUR WHEN USING UPPERCASE WITH QUOTES FOR DATABASE +++ Not sure if this will work as I have not tested end to end. Why create it this way to begin with? DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'HAWK' CONNECT IDENTIFIER IS HAWK; Configuration "DG_CONFIG" created with primary database "HAWK" DGMGRL> show configuration Configuration - DG_CONFIG Protection Mode: MaxPerformance Members: HAWK - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> show database hawk Object "hawk" was not found DGMGRL> show database HAWK Object "hawk" was not found DGMGRL> show database 'HAWK'; Database - HAWK Role: PRIMARY Intended State: OFFLINE Instance(s): hawk1 hawk2 Database Status: DISABLED DGMGRL>
REFERENCE:
CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS database-name CONNECT IDENTIFIER IS connect-identifier;
database-name
The name that will be used by the broker to refer to the primary database.
It must match (case-insensitive) the value of the primary database DB_UNIQUE_NAME initialization parameter.
12c DataGuard / Broker Pitfalls
In a broker configuration, you use the DGConnectIdentifer property to specify a connect identifier for each database.
The connect identifier for a database must: Allow all other databases in the configuration to reach it. Allow all instances of an Oracle RAC database to be reached. Specify a service that all instances dynamically register with the listeners so that connect-time failover on an Oracle RAC database is possible.
The service should NOT be one that is defined and managed by Oracle Clusterware.
A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.
Else, by default, the broker assumes a static service name of db_unique_name_DGMGRL.db_domain and expects the listener has been started with the following content in the listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = port_num))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(SID_NAME=sid_name)
(ENVS="TNS_ADMIN=oracle_home/network/admin")
)
)
As of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared.
Create Configuration Failing with ORA-16698 (Doc ID 1582179.1)
Oracle Data Guard Installation
Goldengate REPORTING
There were performance issues due to a new table being introduced to replication and I was asked to gather number of DMLs on table for 1 day.
Using DBA_TAB_MODIFICATIONS did not meet the requirements since statistics were gather about a week ago and over inflated.
Next thought process was why not use Goldengate since it captures all the changes and report on it.
This may or may not provide the required data if reporting is not properly configured.
Some will tell you to never rollover the report while others will rollover the report on a weekly basis.
From most recent experience, I would rollover report on daily basis because data can always be aggregated.
Here is an example of what I had to deal with.
$ grep -i report dirprm/e_hawk.prm
REPORTCOUNT EVERY 10 MINUTES, RATE
$ grep “activity since” dirtpt/E*.rpt|sort
E_HAWK0.rpt:Report at 2018-02-09 12:28:49 (activity since 2018-02-08 22:01:15)
--- How are you going to get daily activities accurately from aggregation over 2 months time frame?
E_HAWK1.rpt:Report at 2018-02-08 21:48:37 (activity since 2017-11-27 18:24:26)
Corresponding data from the report.
$ grep -A5 “From Table SCOTT.RATETEST” dirrpt/E_*rpt
E_HAWK0.rpt:Report at 2018-02-09 12:28:49 (activity since 2018-02-08 22:01:15) dirrpt/E_HAWK0.rpt:From Table SCOTT.RATETEST: dirrpt/E_HAWK0.rpt- # inserts: 977 dirrpt/E_HAWK0.rpt- # updates: 10439912 dirrpt/E_HAWK0.rpt- # befores: 10439912 dirrpt/E_HAWK0.rpt- # deletes: 0 dirrpt/E_HAWK0.rpt- # discards: 0 -- E_HAWK1.rpt:Report at 2018-02-08 21:48:37 (activity since 2017-11-27 18:24:26) dirrpt/E_HAWK1.rpt:From Table SCOTT.RATETEST: dirrpt/E_HAWK1.rpt- # inserts: 87063 dirrpt/E_HAWK1.rpt- # updates: 821912582 dirrpt/E_HAWK1.rpt- # befores: 821912582 dirrpt/E_HAWK1.rpt- # deletes: 0 dirrpt/E_HAWK1.rpt- # discards: 0
How did I end up getting the data?
From Goldengate report above which is better as it provides the exact data processed by Goldengate.
I was lucky! Extract was restarted when table was added and removed.
Example of Better Configuration:global_ggenv.inc
STATOPTIONS RESETREPORTSTATS REPORT AT 00:01 REPORTROLLOVER AT 00:01 REPORTCOUNT EVERY 60 MINUTES, RATE DISCARDROLLOVER AT 00:01
e_hawk.prm
EXTRACT E_HAWK
EXTRACT e_hawk
EXTTRAIL ./dirdat/bb
-- CHECKPARAMS
INCLUDE ./dirprm/global_macro.inc
INCLUDE ./dirprm/global_dbenv.inc
INCLUDE ./dirprm/global_ggenv.inc
Reference:
REPORTROLLOVER
Use the REPORTROLLOVER parameter to force report files to age on a regular schedule, instead of when a process starts.
For long or continuous runs, setting an aging schedule controls the size of the active report file and provides a more predictable set of archives that can be included in your archiving routine.
Report statistics are carried over from one report to the other. To reset the statistics in the new report, use the STATOPTIONS parameter with the RESETREPORTSTATS option.
DBA_TAB_MODIFICATIONS
INSERTS/UPDATES/DELETES - Approximate number since the last time statistics were gathered.
PURGEOLDEXTRACTS Not Purging Trail Files Part2
If you read the post PURGEOLDEXTRACTS Not Purging Trail Files, you will find the solution is to replace syntax from mgr.prm as shown below:
Replace PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1 With PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
I found the solution by luck vs correct analysis; hence, the adage “Better to be lucky than good.”
Recently, the same issue occurred again for another environment and the solution was just the opposite.
PURGEOLDEXTRACTS dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 30 -- PURGEOLDEXTRACTS /DBFS/ggs/dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 30
Why is that!
No convention and inconsistency.
Here are the details.
--- How is manager configured? GGSCI> send manager GETPURGEOLDEXTRACTS Sending GETPURGEOLDEXTRACTS request to MANAGER ... --- Manager is configured with trail pointing to /DBFS PurgeOldExtracts Rules Fileset MinHours MaxHours MinFiles MaxFiles UseCP /DBFS/ggs/dirdat/* 24 0 1 0 Y OK --- Extract trail showing from $GG_HOME/dirdat Extract Trails Filename Oldest_Chkpt_Seqno IsTable IsVamTwoPhaseCommit /u01/app/gg/12.2.0/dirdat/aa 16285 --- How was the extract created? GGSCI> send e* status Sending STATUS request to EXTRACT E_HAWK ... EXTRACT E_HAWK (PID 40932) Current status: Recovery complete: At EOF Current read position: Sequence #: 16285 RBA: 27233729 Timestamp: 2018-01-25 21:01:35.000450 Extract Trail: dirdat/aa --- This is how the trail is defined when extract was created GGSCI> info e* EXTRACT E_HAWK Last Started 2018-01-25 21:22 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Process ID 40932 Log Read Checkpoint File dirdat/aa000016286 2018-02-01 14:42:29.000124 RBA 29233729 GGSCI> exit --- From $GG_HOME, dirdat is using symbolic link to /DBFS $ ls -ld dir* lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirchk -> /DBFS/ggs/dirchk lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dircrd -> /DBFS/ggs/dircrd lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirdat -> /DBFS/ggs/dirdat lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirdef -> /DBFS/ggs/dirdef lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirdmp -> /DBFS/ggs/dirdmp lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirout -> /DBFS/ggs/dirout drwxr-xr-x 2 ggsuser oinstall 4096 Jan 26 13:49 dirpcs lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirprm -> /DBFS/ggs/dirprm lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirrpt -> /DBFS/ggs/dirrpt lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirsql -> /DBFS/ggs/dirsql lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirtmp -> /DBFS/ggs/dirtmp lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirwlt -> /DBFS/ggs/dirwlt lrwxrwxrwx 1 ggsuser oinstall 23 Mar 18 2017 dirwww -> /DBFS/ggs/dirwww
In conclusion, PURGEOLDEXTRACTS location should be defined the same as the extract.
Isn’t that intuitive?
Oracle should make this a MOS Doc ;=)