Verifying Database Configuration at GoldenGate source(OIPA)

  1. Check if archivelog is enabled for the Database by issuing below from Oracle command prompt.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

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

ARCHIVELOG

  1. If the above result is NOARCHIVELOG, issue the below commands in the database as SYS to enable it else go to STEP-3.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 4526317568 bytes

Fixed Size 2297296 bytes

Variable Size 973079088 bytes

Database Buffers 3539992576 bytes

Redo Buffers 10948608 bytes

Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

  1. Verify the database is in archivelog mode now

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

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

ARCHIVELOG

  1. Check if force logging is enabled

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOGGING

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

NO

  1. If NO issue the below command to enable the force logging else go to STEP 6

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

  1. Check if supplemental logging is enabled

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

--------

NO

  1. If NO issue the below commands to enable supplemental logging

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Performance Recommendations

REDO log size Recommendation

  1. Set the size of the redo log file in such a way that a file switch should take place in an interval of 20-30 minutes.
  2. Log file size in Performance Environment: 2048 MB
  3. Run the Query 1 and from results, choose one member (redo log file) to be replaced with new Redo log file.
Query 1: 
Select GROUP#, MEMBER from v$logfile where group# in (select GROUP# from v$log where STATUS ='INACTIVE');
Example: In this example, Group# 2 log file was chosed.
GROUP#         MEMBER
3              /scratch/oipa/app/oipa/oradata/orcl/redo03.log
2              /scratch/oipa/app/oipa/oradata/orcl/redo03.log
To add new redo log file with 2 GB size and in the example, it was added with Group 4
Query 2: 
  alter database add logfile <next number of group_number> '<path>/redo#.log' size 2048M;
Example: 
  alter database add logfile group 4 '/scratch/oipa/app/oipa/oradata/orcl/redo04.log' size 2048M;
Drop the existing inactive member/log file(Ex: Group 2) and group resulted in 1st step.Query
Query 3:
 alter database drop logfile group 2;
Now, activate the newly added log file by running below queries.
Query 4:
  alter system checkpoint;
Query 5:
 alter system switch logfile;
 To verify the status of changed log file to be in status “CURRENT”
Query 6:
 select GROUP#, STATUS FROM V$LOG;

 

Number of Processes to be Sized Appropriately

Size it to 1000 as per the Perf analysis it will use those many processes.

SQL> select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('processes');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE

processes 242 332 400

SQL to change the processes limit:

alter system set processes=1000 scope=spfile;

Setting SGA Size to 15 GB

SHOW SGA;

show parameter spfilecreate pfile from spfile;

alter system set sga_max_size=15G scope=spfile;

alter system set sga_max_size=8000m scope=spfile;

Additional Tuning Recommendations

ALTER SYSTEM SET SESSION_CACHED_CURSORS=4000 SCOPE=SPFILE;

ALTER SYSTEM SET COMMIT_LOGGING = IMMEDIATE;

ALTER SYSTEM SET COMMIT_WAIT = NOWAIT;

ALTER SYSTEM SET CURSOR_SHARING = FORCE;

Note: These recommendations were made based on the Performance analysis of ODS application with adequate load. Possible tuning can be done if needed.

 

 

 

Oracle Insurance Logo Copyright © 2017, Oracle and/or its affiliates. All rights reserved. About Oracle Insurance | Contact Us