ora-semsem: 2022

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

How to recover standby database from a missing archive log gap

02-08-2022

#how to recover standby database from a missing archive log gap

#Hossam Note

#if there is a Gap on DG there is 2 scenarios

#1- missing archivelogs on standby but it exist on prod this can be fixed by using rsync and catalog start with

#2-missing archivelogs on standby but its NOT exist on prod this can be fixed by below

https://techgoeasy.com/recovering-standby-database-fro/

#On the standby database check the current SCN

SQL> col current_SCN for 99999999999999999999;

SQL> select current_scn from v$database;

CURRENT_SCN

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

111050674405375

#On the primary database create the needed incremental backup from the above SCN

rman target /

RMAN>

{

allocate channel c1 type disk;

BACKUP INCREMENTAL FROM SCN 111050674405375 DATABASE FORMAT '/tmp/inc_standby_%U';

}

#Cancel managed recovery at the standby database

SQL> alter database recover managed standby database cancel;

Database altered

#scp the backup files to standby server to /tmp folder.

scp -rp oraprod@

#Apply the Incremental Backup to the Standby Database

rman target /

RMAN>RECOVER DATABASE NOREDO;

#Put the standby database back to managed recovery mode.

qlplus "/ as sysdba"

SQL>recover managed standby database disconnect;

Media recovery complete.

#On the primary create new standby controlfile

alter database create standby controlfile as '/tmp/standby01.ctl';

#Copy the standby control file to the standby site. Shutdown the standby database and replace the standby control files and restart the standby database in the managed recovery mode using the below command

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/standby01.ctl';

#Mount the standby

RMAN> ALTER DATABASE MOUNT;

#On STANDBY database, clear all standby redo log groups:

select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;

GROUP#

----------

ALTER DATABASE CLEAR LOGFILE GROUP 10;

ALTER DATABASE CLEAR LOGFILE GROUP 11;

ALTER DATABASE CLEAR LOGFILE GROUP 12;

ALTER DATABASE CLEAR LOGFILE GROUP 13;

#if dont clear restart standby db

#Now you can start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

#if needed

cd /oradata/proddata19/LOCALERP/archive/

rsync -avzhe ssh --progress oraprod@10.143.250.164:/oradata/proddata19/LOCALERP/archive/ .

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