Configuring GGSCI Command Security CMDSEC

Configuring GGSCI Command Security CMDSEC

You can establish command security for Oracle GoldenGate to control which users have access
to which Oracle GoldenGate command line functions. For example, you can allow certain users to issue
INFO and STATUS commands, while preventing their use of START and STOP commands. Security
levels are defined by the operating system's user groups.

To implement security for Oracle GoldenGate commands, you create a CMDSEC file in the Oracle
GoldenGate directory. Without this file, access to all Oracle GoldenGate commands is granted
to all users.


As GoldenGate administrator you have team of GoldenGate users who has different levels of
skill sets and responsibilities ,how do you manage these Users with correct responsibilities
is in GoldenGate classic 19c ?

CMDSEC (Command line GGSCI Security)

For e.g. A person with monitoring responsibility should not able to use START and STOP commands
But the same person should able to use INFO and status commands. Remember one thing this is
operating system's users security levels. So you need to have these users defined at operating
system level first and then only you will able to use CMDSEC.


GGSCI command security may be used to grant or deny users to specific commands within
the utility. The CMDSEC file contains space or tab delimited data rows that specifies
what commands users and groups may execute. The format of each data rows is:

command_name command_object OS_group OS_user {YES | NO}

[oracle@ggate1 ogg_trg]$ vi CMDSEC

#command_Name Command_Object  OS_Group  OS_User Allow
STATUS         *               dba       *       NO
START          *               dba       *       YES
STOP           *               dba       *       YES
SHELL          *               *         *       NO
SH             *               *         *       NO
*              *               root      root    YES


[oracle@ggate1 ogg_trg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


When setting up CMDSEC, important things to consider:

1) The oracle user requires full access to all files and directories.

2) Only the oracle user must be allowed to start processes. Processes are owned by the user running GGSCI, and no other users; including oracle, can stop them. For example, if the user Jeff in the sysops group were allowed to start Extracts or Replicats, he would be the only person (other than root) allowed to stop them.

3) The dba group and other users monitoring the instance require write access to the file ggserr.log.

4) The dba group and other users monitoring the instance require execute access to the ggsci object.

5) The dba group and other users monitoring the instance require full access to the dirchk directory in order to execute the status command.

6) The dba group and other users monitoring the instance require read and execute access to the dirrpt directory in order to execute the view report command.



GGSCI (ggate1) 1> status
ERROR: Command not authorized for this user.

GGSCI (ggate1) 2> SHELL ls -lart
ERROR: Command not authorized for this user.

GGSCI (ggate1) 3> SH ls -lart
ERROR: Command not authorized for this user.

Go ahead and configure CMDSEC  and explore !!!

Oracle Apps cheat sheet

Here are some tips for the Oracle Applicaitons DBA which can be used in day to day life.

# Many a times dba need to check which programs trace is enabled by developers on Dev,test or some times in Production instance. Following query will help in identifying the same.

select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME, v.
level_value LEVEL_VAL, v.profile_option_value VALUE
from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%Debug%')
and v.profile_option_value ='Y'

To get the Internal Concurrent Manager log file name you can execute the next sql:
SELECT process_start_date, logfile_name
FROM fnd_concurrent_processes fcp
WHERE manager_type = 0
order by creation_date;
.
#script to find trace file from Concurrent Request id

column traceid format a8
  column tracename format a80
  column user_concurrent_program_name format a40
  column execname format a15
  column enable_trace format a12
  set lines 80
  set pages 22
  set head off

  SELECT 'Request id: '||request_id ,
  'Trace id: '||oracle_Process_id,
  'Trace Flag: '||req.enable_trace,
  'Trace Name:
  '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
  'Prog. Name: '||prog.user_concurrent_program_name,
  'File Name: '||execname.execution_file_name||
execname.subroutine_name ,
  'Status : '||decode(phase_code,'R','Running')
  ||'-'||decode(status_code,'R','Normal'),
  'SID Serial: '||ses.sid||','|| ses.serial#,
  'Module : '||ses.module
  from fnd_concurrent_requests req, v$session ses, v$process proc,
  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
  fnd_executables execname where req.request_id = &request
  and req.oracle_process_id=proc.spid(+)
  and proc.addr = ses.paddr(+)
  and dest.name='user_dump_dest'
  and dbnm.name='db_name'
  and req.concurrent_program_id = prog.concurrent_program_id
  and req.program_application_id = prog.application_id
  and prog.application_id = execname.application_id
  and prog.executable_id=execname.executable_id;
.

How does one enable tracing in Self Service Applications?
Solution
To enable tracing on self service applications do the following:
1. Go to responsibility = System Administrator
2. Navigate to:  Profile > System
3. In User text box: Enter User name
4. Query the Profile: FND: Diagnostics
5. Set the FND:Diagnostics profile to Yes at User level.
6. Login into Self Service under the same user used to set the profile value.
7. Click the Diagnostic icon at the top of the page. It shows two options:
Show Log
Set Trace Level
Select 'Set Trace Level'
Click Go.
It shows a page with a set of options. Set Trace Level - This is like a forms trace - with several options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues.
Trace with binds and waits - combines both of the above
8. Then select trace level, normally 'Trace with binds and waits' (level 12 equivalent), then click Save
9. Note the first Trace ID, then Click tabs to go back to Home page to reproduce the issue
10. Once completed, go back into Diagnostics and set trace level to 'Disable Trace'. Note the Trace ID's listed
Select the Diagnostic icon.
Click on option: Set Trace Level
Click Go
Select : Disable Trace
11. To determine where the raw trace file is located.
From SQLPlus:
SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest';
12. run TKPROF procedure on the raw trace file

Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
q.concurrent_queue_name,
q.user_concurrent_queue_name,
q.target_node,
q.max_processes,
q.running_processes,
running.run running,
pending.pend,
Decode(q.control_code, 'D', 'Deactivating',
'E', 'Deactivated',
'N', 'Node unavai',
'A', 'Activating',
'X', 'Terminated',
'T', 'Terminating',
'V', 'Verifying',
'O', 'Suspending',
'P', 'Suspended',
'Q', 'Resuming',
'R', 'Restarting') status
FROM (SELECT concurrent_queue_name,
COUNT(phase_code) run
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'R'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) running,
(SELECT concurrent_queue_name,
COUNT(phase_code) pend
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'P'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) pending,
apps.fnd_concurrent_queues_vl q
WHERE q.concurrent_queue_name = running.concurrent_queue_name(+)
AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
AND q.enabled_flag = 'Y'
ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)),
Sign(q.max_processes) DESC,
q.concurrent_queue_name,
q.application_id;
#What changed in my database
Here is the query to identify the DB objects that may have been changed.
SELECT   owner, object_type, object_name, TRUNC (created) created,
         TRUNC (last_ddl_time) last_ddl_time
    FROM dba_objects
   WHERE (owner LIKE 'XX%' OR owner LIKE 'APPS' )
     AND TRUNC (last_ddl_time) between /*last_day(trunc(sysdate)) - 30*/ '07-DEC-2011' and trunc(sysdate)
ORDER BY last_ddl_time DESC;

# Many times concurrent request run slow ,and you wanted to investigate why they are running slow ,does particular table has stats gathered or not . Following code will help you in finding out the same.

set lines 200
set serveroutput on
exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));

Example :
SQL> exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));
Enter value for tableowner: applsys
Enter value for tablename: fnd_concurrent_requests
===================================================================================================
Table   FND_CONCURRENT_REQUESTS
===================================================================================================
last analyzed     sample_size num_rows            blocks
12-08-2011 21:08  23931       23931               236172
Index name                    last analyzed     num_rows  LB      DK       LB/key  DB/key  CF
----------------------------------------------------------------------------------------------------
FND_CONCURRENT_REQUESTS_F1    12-08-2011 21:08  23932     327     105      3       33      3507
FND_CONCURRENT_REQUESTS_N1    12-08-2011 21:10  23934     211     22333    1       1       20325
FND_CONCURRENT_REQUESTS_N10   12-08-2011 21:08  23932     346     4        86      584     2339
FND_CONCURRENT_REQUESTS_N11   12-08-2011 21:08  23932     298     22653    1       1       20038
FND_CONCURRENT_REQUESTS_N2    12-08-2011 21:10  23934     159     8        19      300     2406
FND_CONCURRENT_REQUESTS_N3    12-08-2011 21:08  23932     460     19179    1       1       16543
FND_CONCURRENT_REQUESTS_N4    12-08-2011 21:09  23932     309     23928    1       1       20027
FND_CONCURRENT_REQUESTS_N5    12-08-2011 21:09  271       153     192      1       1       230
FND_CONCURRENT_REQUESTS_N6    12-08-2011 21:09  23933     350     39       8       130     5084
FND_CONCURRENT_REQUESTS_N7    12-08-2011 21:10  23933     190     9        21      267     2406
FND_CONCURRENT_REQUESTS_N8    12-08-2011 21:10  23934     474     22       21      242     5340
FND_CONCURRENT_REQUESTS_N9    12-08-2011 21:10  23934     229     8        28      300     2406
FND_CONCURRENT_REQUESTS_U1    12-08-2011 21:08  23932     285     23932    1       1       20026
----------------------------------------------------------------------------------------------------
Histogram  Stats
Schema         Table Name                     Status      last analyzed     Column Name
----------------------------------------------------------------------------------------------------
APPLSYS        FND_CONCURRENT_REQUESTS        present     08-12-2011 21:08  PHASE_CODE
APPLSYS        FND_CONCURRENT_REQUESTS        present     08-12-2011 21:08  STATUS_CODE

Legend:
LB : Leaf Blocks
DK : Distinct Keys
DB : Data Blocks
CF : Clustering Factor
PL/SQL procedure successfully completed.