ora-semsem

Sunday, May 21, 2023

Monday, March 20, 2023

Automate your job with Python

Sunday, August 7, 2022

certificate Import Apache Tomcat

cd /opt/tomcat/bin

./shutdown.sh

cd /opt/tomcat/certificate

cp -rp keystore.jks keystore.jks_bkp

mv start_***.com.eg.pfx start_****.com.eg.pfx_bkp

copy new certificate start_****.com.eg.pfx in the same path

keytool -importkeystore -srckeystore start_***.com.eg.pfx -srcstorepass ******* -srcstoretype pkcs12 -destkeystore keystore.jks -deststoretype jks -deststorepass ******

cd /opt/tomcat/bin

startup.sh

#to check password in server.xml

cd /opt/tomcat/conf

vi server.xml

Fix Output Post-processor issues

select DEVELOPER_PARAMETERS from FND_CP_SERVICES

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

#production

J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m

#upgrade env

update FND_CP_SERVICES

set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx4096m'

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

#production worked on production with higher value mx4096m

update FND_CP_SERVICES

set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx3072m'

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

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

#fix

CONC-POST-PROCESSING RESULTS into error "GC Overhead Limit Exceeded" (Doc ID 2573449.1)

Then, in System Administrator resp, navigate to Profile > System

- set Concurrent:OPP Process Timeout = 43200

- set Concurrent:OPP Response Timeout = 43200

(these values are for 12 hours in seconds)

#old

#CONC-POST-PROCESSING RESULTS into error "GC Overhead Limit Exceeded" (Doc ID 2573449.1)

https://community.oracle.com/mosc/discussion/2796412/java-lang-outofmemoryerror-gc-overhead-limit-exceeded

#R12: Various Programs End With Warning: The Output Post-processor is Running But Has Not Picked up This Request (Doc ID 1224684.1)

Reclaim Oracle Database free space

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

Enable Active Dataguard In Physical Standby Database

#How To Enable Active Dataguard In Physical Standby Database

https://dbaclass.com/article/enable-active-dataguard-physical-standby-database/

# 1- Cancel the media recovery on physical standby.

SQL> alter database recover managed standby database cancel;

# 2- Open the database[PHYSICAL STANDBY]

SQL> alter database open;

Database altered

# 3- Start media recovery with real-time log apply[PHYSICAL STANDBY]

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

#4- Check the database status:[PHYSICAL STANDBY]

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

PRODDB READ ONLY WITH APPLY

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

RFS IDLE 0

RFS RECEIVING 510

RFS IDLE 0

RFS IDLE 4178

MRP0 APPLYING_LOG 510 --->>>> MRP PROCESS

Oracle Dataguard Monitor

# dataguard monitor

# DR notification

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME >= (SYSDATE - 1) GROUP BY THREAD# ORDER BY THREAD#;

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES' and COMPLETION_TIME

>= (SYSDATE - 1) and DEST_ID=2 GROUP BY THREAD# ORDER BY THREAD#;

#select database role

SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

#status of background processes in a standby database (on standby)

select process, client_process,thread#,sequence#,status from v$managed_standby;

#FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY: monitor dataguard

select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from

(SELECT MAX(SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

(SELECT MAX(SEQUENCE#) LOG_APPLIED

FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

#example (Normal)

LOG_GAP

----------

#LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED:

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM

(select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#

FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history

WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

#CHECK THE MESSAGES/ERRORS IN STNADBY DATABASE: ( Run on standby)

set pagesize 2000

set lines 2000

col MESSAGE for a90

select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;

#check lag ( on standby)

set lines 1000

select name,value from v$dataguard_stats;

#Example

NAME VALUE

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

transport lag +00 00:00:00

apply lag +00 00:00:00

apply finish time +00 00:00:00.000

estimated startup time 8

#which archive sequence number comes from the Primary database lastly and which is last applied in Standby ( on standby)

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

#Example

Thread Last in Sequence Last Applied Sequence Difference

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

1 19642 19642 0

# archive gap in dataguard

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

#applied archivelogs in dataguard,

select thread#,sequence#,first_time,next_time,applied from gv$archived_log where applied='YES';

# max sequence of dataguard

select thread#,max(sequence#) from gv$archived_log group by thread#;

#To see all parameters of Oracle dataguard, you can execute following query.

set linesize 500 pages 0

col value for a80

col name for a50

select name, value from v$parameter

where name in ('db_name','db_unique_name','log_archive_config',

'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3',

'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'remote_login_passwordfile',

'log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert',

'log_file_name_convert', 'standby_file_management') order by 1;

##################################troubleshooting#############################################################################

#stop dataguard

alter database recover managed standby database cancel;

#start dataguard

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"

FROM V$LOG_HISTORY

GROUP BY TRUNC(FIRST_TIME)

ORDER BY TRUNC(FIRST_TIME) DESC

#Register Data Guard standby redo log file tips( this will register all arch logs in this path )

http://www.dba-oracle.com/t_register_standby_redo_log_files.htm

LOCALERP - Primary database

Error: ORA-16724: cannot resolve gap for one or more members

LOCALERPST - Physical standby database

Warning: ORA-16809: multiple warnings detected for the member

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40863_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40864_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40865_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40866_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_62960_1077804461.arc';

1_62960_1077804461.arc

rman> catalog start with '/oradata/proddata19/LOCALERP/archive';

catalog start with '/oradata/proddata19/LOCALERP/archive/';

10 16 * * * /bin/sh /home/oraprod/del_arch.sh > /home/oraprod/1