Golden Gate Activity Logging Tracing

Enable tracing are the TRACE(1)/TRACE2 -- OGG Activity Logging Tracing (Doc ID 1204284.1)

GGSCI (rac1) 3> send extract EXT1 trace /tmp/trace_me.trc

GGSCI (rac1) 5> send extract EXT1 trace off

ACTIVITY TRACING -- Oracle GoldenGate - Version 11.1.1.0.0 and later

1. gglog-traceini.xml --  this will generate the application level tracini logging

<?xml version="1.0" ?>

<configuration reset="true">

<appender name="traceini" class="RollingFileAppender">

<param name="BufferedIO" value="false" />

<param name="Append" value="true" />

<param name="File" value="traceLog_%I_%A" />

<param name="MaxBackupIndex" value="99" />

<param name="MaxFileSize" value="10MB" />

<layout class="PatternLayout">

<param name="Pattern" value="%d{%m/%d %H:%M:%S} [%C{1}:%L] %m%n" />

</layout>

</appender>

<logger name="gglog.std">

<level value="all" />

<appender-ref name="traceini" />

</logger>

<logger name="gglog.std.utility">

<level value="off" />

<appender-ref name="traceini" />

</logger>

</configuration>

2. gglog-full.xml -- this file will produce a full logging and generate very large trace file very quickly so it should only be used where EXTRACT/REPLICAT abend immediately after restart:

<?xml version="1.0"?>

<configuration reset="true">

<appender name="myAppender" class="RollingFileAppender">

<param name="File" value="gglog-%I.log"/>

<param name="MaxBackupIndex" value="9"/>

<param name="BufferedIO" value="false"/>

<layout class="PatternLayout">

<param name="Pattern" value="%d{%Y-%m-%d %H:%M:%S} %-5p|%-30.30c|%t|%5L %-24.24C{2} | %m%n"/>

</layout>

</appender>

<root>

<level value="all"/>

<appender-ref name="myAppender"/>

</root>

</configuration>

3. gglog-extract-log.xml -- this file will produce a "TLTRACE DEBUG" equivalent logging  for Oracle redo log reding Extract  and large number of trace files very quickly so it should only be used where EXTRACT abends immediately after restart.  Please also make sure you have "tranlogoptions nofastreads" added to the Extract paramter file

<?xml version="1.0"?>

<configuration reset="true">

<!--

/- ============================================================= -\

!- g g l o g - D E B U G . x m l -|

!- -|

!- Activity Logging control file -|

!- ============================================================= -|

!- This file was generated automatically as part of running -|

!- an executable created with a "Debug" configuration. -|

!- -|

!- This simple control file will provides basic output from -|

!- the Activity Logging subsystem for each program to a file -|

!- called, gglog-%I.txt, where '%I' is either the application -|

!- name, like 'ggsci', or the EXTRACT/REPLICAT group name. -|

!- A new file is created each time the application executes, -|

!- and up to nine versions of each file are kept on disk. -|

!- -|

!- For customizing this file, please consult the Activity -|

!- Logging design document. -|

\- ============================================================= -/

! -->

<!---

! Rolling appender for tracking each application execution

-!-->

<appender name="myAppender" class="RollingFileAppender">

<param name="File" value="gglog-%I_%A.log"/>

<param name="MaxBackupIndex" value="99"/>

<param name="MaxFileSize" value="1000MB"/>

<param name="BufferedIO" value="false"/>

<!---

! Output is formatted as:

! date-time LEVEL|logger-name | line-number source-module | Message\n

!

! Consult the Activity Logging design document or include/gglog.h

! for more details.

-!-->

<layout class="PatternLayout">

<param name="Pattern" value="%t %5L %-24.24C{2} | %m%n"/>

</layout>

</appender>

<!---

! Set up GoldenGate standard logging

!

! Only warnings for SYS and UTL logging.

! All logging enabled for APP.

-!-->

<logger name="gglog.std.utility">

<level value="off"/>

</logger>

<logger name="gglog.std.utility.calltrace">

<level value="off"/>

</logger>

<logger name="gglog.std.system">

<level value="off"/>

</logger>

<logger name="gglog.std.system.calltrace">

<level value="off"/>

</logger>

<logger name="gglog.std.application">

<level value="off"/>

</logger>

<logger name="gglog.std.application.calltrace">

<level value="off"/>

</logger>

<logger name="ggapp.parmscn">

<level value="off"/>

</logger>

<logger name="ggstd.ptrace">

<level value="off"/>

</logger>

<logger name="er.redo.ora">

<level value="all"/>

</logger>

<logger name="er.redo.ora.data">

<level value="all"/>

</logger>

<logger name="er.redo.ora.rtc">

<level value="off"/>

</logger>

<logger name="er.redo.ora.rtcfm">

<level value="off"/>

</logger>

<logger name="er.redo.ora.thread">

<level value="all"/>

</logger>

<logger name="er.redo.ora.thread.checkpoint">

<level value="off"/>

</logger>

<logger name="er.redo.ora.thread.blocks">

<level value="off"/>

</logger>

<logger name="er.redo.ora.thread.logswitch">

<level value="all"/>

</logger>

<logger name="er.redo.ora.thread.unpack">

<level value="all"/>

</logger>

<!---

! Everything goes to our appender

-!-->

<root>

<appender-ref name="myAppender"/>

</root>

</configuration>

4. tracing same as pre-v11.1 parameter "TRANLOGOPTIONS LOGSWITCHMSG ON"

The parameter is nolonger supported in v11.1 and up.  The redo log switch may be traced with activity logging: download the attach the file "Template to generate logswitch information" and follow the instruction bellow to rename it.

5. tracing Oracle redo error only.  

<?xml version="1.0"?>

<configuration reset="true">

<!---

! Rolling appender for tracking each application execution

-!-->

<appender name="binaryAppender" class="RollingFileAppender">

<param name="File" value="gglog-%I_%A.dump"/>

<param name="MaxBackupIndex" value="10"/>

<param name="MaxFileSize" value="500MB"/>

<param name="BufferedIO" value="false"/>

<layout class="BinaryLayout"/>

</appender>

<logger name="er.redo.ora">

<level value="warn"/>

</logger>

<logger name="er.redo.ora.thread">

<level value="warn"/>

</logger>

<logger name="er.redo.ora.thread.logswitch">

<level value="error"/>

</logger>

<root>

<level value="warn"/>

<appender-ref name="binaryAppender"/>

</root>

</configuration>

6.  For a Sql Server specific trace, create an xml file with the following lines:

<logger name="vam.sqlserver">

<level value="debug"/>

</logger>

7. For retrace command for .dmp files

CD $OGGHOME

> retrace <gglog-debug.xml> <gglog-extract.dmp>

**Contents of gglog-debug.retrace**

<?xml version="1.0"?>

<configuration reset="true">

<appender name="traceini" class="RollingFileAppender">

<param name="BufferedIO" value="false"/>

<param name="Append" value="true"/>

<param name="File" value="baswamy_retrace_traceLog_%I_%A.log"/>

<param name="MaxBackupIndex" value="20"/>

<param name="MaxFileSize" value="500MB"/>

<layout class="PatternLayout">

<param name="Pattern" value="%d{%Y-%m/%d %H:%M:%S} %-22t|%-5p|%5L %-24.24C{2} | %m%n"/>

</layout>

</appender>

<root>

<appender-ref name="traceini"/>

<level value="all"/>

</root>

</configuration>

Upgrade APEX to Latest Version in Oracle Database

1. Stop database 
========================================================
srvctl status database -d APEXDB
srvctl stop database -d APEXDB

2. cold backup 
3. show parameter job queue processes 
4. download and copy software 
5. stop dataguard  <dr machine> - <username>

========================================================

6. Set parameter to disable data guard 
alter system set log_archive_dest_state_2=DEFER scope=both;  
alter system set db_recovery_file_dest_size=100G scope=both;  

7. Start Database and Check files details 
========================================================
set line 250 pagesize 1000
col FILE_NAME for a90
select FILE_ID, FILE_NAME , STATUS, BYTES/1024/1024/1024 GB from dba_data_files order by GB desc ;

select NAME from v$controlfile;

col MEMBER for a90
select member from v$logfile; 

select name from v$tempfile;

set lines 100
col name format a60
select 
   name,  floor(space_limit/1024/1024/1024) "Size GB",
  ceil(space_used/1024/1024/1024) "Used GB"
from   v$recovery_file_dest
order by    name;

8. CREATE RESTORE POINT
========================================================
select * from v$restore_point;

SQL> create restore point BEFORE_UPGRADE guarantee flashback database;

9. CHECK CURRENT Setup 
========================================================
col VERSION_NO for a30
col API_COMPATIBILITY for a30
col PATCH_APPLIED for a30
set lines 150
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
select * from apex_release;

alter system set standby_file_management=AUTO scope=both sid='*';
alter database create datafile '/u01/app/oracle/product/12.1.0.2/dbhome_APEXDB/dbs/UNNAMED00025' as '+RECO_P/APEXDB/DATAFILE/apexdb.11482.968172777';


10. DISABLE APEX
========================================================
EXEC DBMS_XDB.SETHTTPPORT(0);    --- This command is enough  
exec DBMS_XDB_CONFIG.setHTTPSPort(0);
exec DBMS_XDB_CONFIG.setHTTPPort(0);
exec DBMS_XDB_CONFIG.setFTPPort(0);


SQL> STARTUP RESTRICT

11. INSTALL NEW APEX
========================================================

$ cd /u03/tmp/apex 

SQL> create tablespace apex19 datafile size 4g autoextend on next 100m ;

SQL> @apexins.sql apex19 apex19 TEMP  /i/

SQL> @apxchpwd.sql  

SQL> @apex_epg_config.sql /u03/tmp

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;


12. ENABLE APEX
========================================================
SQL> col VERSION_NO for a30
SQL> col API_COMPATIBILITY for a30
SQL> col PATCH_APPLIED for a30
SQL> set lines 150
SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080); 
SQL> select * from apex_release;
SQL>

SQL> BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_180100',
                           principal_type => xs_acl.ptype_db));
END;
/


13. RESTART DATABASE
========================================================
SQL> shut immediate

$ srvctl start database -d APEXDB

14. DELETE RESTORE POINT
========================================================
SQL> drop restore point BEFORE_UPGRADE;

15. DROP OLD APEX USER
========================================================
SQL > SELECT username  FROM dba_users  WHERE 
(  username LIKE 'FLOWS\_______' ESCAPE '\'       OR username LIKE 'APEX\_______' ESCAPE '\' )   
AND username NOT IN ( SELECT schema  FROM dba_registry   WHERE comp_id = 'APEX' );

SQL> DROP USER APEX_050000 CASCADE; -- drop old apex user 
SQL> EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000');


SQL> EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000');
BEGIN DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000'); END;

Restore/Clone database from RMAN Backup with wallet

Database restore test for <database name> : 


1.            Copy wallet files to wallet location and test if wallet is opening. 
2.            Set RMAN configuration parameters. 

RMAN> set dbid=139425645454
RMAN> set decryption identified by 'password';
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES128';


run {
        set until sequence 520477 thread 1;
                                set until sequence 498092 thread 2;
        duplicate target database to <target db name>
        nofilenamecheck;
} 



alter system set db_file_name_convert='+DATA/test1/DATAFILE/','+DATA/test2/DATAFILE/';
alter system set log_file_name_convert='+DATA/test1/DATAFILE/','+DATA/test2/DATAFILE/';


run {
                set until logseq 515519;
                duplicate target database to test2
                nofilenamecheck;
}

RMAN> 
set decryption identified by 'password';
set ENCRYPTION identified by 'password';

executing command: SET decryption

RMAN> show encryption algorithm;

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";


$ 
$ ./rman catalog rmancat/passwd auxiliary sys/test@clone_db

-- Query to check Wastage Space in table

select * from
(select s.owner,segment_name,to_char(last_analyzed,'DD-MON-YY hh24:mi:ss') last_analyzed,round(bytes/1024/1024,2) "ACTUAL_SIZE",
round(avg_row_len*num_rows/1024/1024,2) "RIGHT_SIZE",
CASE WHEN round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2) > 70 THEN '<span class="threshold-critical">'||round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2)||'</span>' ELSE '<span class="threshold-ok">'||round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2)||'</span>' END "%WASTAGE",
to_char(o.last_ddl_time,'DD-MON-YY hh24:mi:ss') LAST_DDL_TIME,
t.DEGREE,t.LOGGING,t.tablespace_name
from dba_segments s,dba_tables t,dba_objects o
where s.owner=t.owner
and t.owner=o.owner
and t.table_name=o.object_name
and s.segment_name=t.table_name
and s.bytes/1024/1024 > 1000
and t.owner='TESTDTA' 
and t.table_name='F5707002'
ORDER BY "%WASTAGE" desc)
where rownum < 101;