Verifying Database Configuration at GoldenGate source(OIPA)
- 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
- 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.
- Verify the database is in archivelog mode now
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
- Check if force logging is enabled
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FORCE_LOGGING
---------------------------------------
NO
- If NO issue the below command to enable the force logging else go to STEP 6
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
-
Check if supplemental logging is enabled
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
- 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
- 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.
- Log file size in Performance Environment: 2048 MB
- 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.