Shrink datafile after 'ORA-03297'

Problem

When try to resize datafile following error occur

SQL> ALTER DATABASE DATAFILE '/u01/PROD/data/system01.dbf' RESIZE 10G;
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Solution

1. Check the tablespace sizes

SQL> set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

2. Find High Watermark of tablespace

SQL>select tablespace_name,
file_id,
file_name DATA_FILE_NAME,Allocated_MBYTES,
High_Water_Mark_MBYTES,
FREE_MBYTES,
trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",
trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizable
from
(
select ddf.tablespace_name tablespace_name,
ddf.file_id file_id,
ddf.file_name file_name,
ddf.bytes/1024/1024 Allocated_MBYTES,
trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,
FREE_MBYTES
from dba_data_files ddf
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
left join (
    select file_id, sum(bytes/1024/1024) FREE_MBYTES
    from dba_free_space
    group by file_id
    ) free on ddf.file_id = free.file_id
join (
    select file_id, max(block_id+blocks) hwm
    from dba_extents
    group by file_id
    ) ex on ddf.file_id = ex.file_id
where 1=1
and ddf.tablespace_name='SYSTEM'
order by ddf.tablespace_name, ddf.file_id);

3. List the segments that have extents near the high watermark for given datafile

SQL>select file_name data_file_name,
segment_type,
owner||'.'||segment_name segment_name,
partition_name,
block_id,
blockId_Mbytes,
decode (segment_type,'TABLE','ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE AA_DATA;','INDEX','ALTER INDEX '||owner||'.'||segment_name||' REBUILD ONLINE;')sc
from
(
select
de.owner owner,
de.segment_name segment_name,
de.segment_type segment_type,
de.block_id block_id,
DE.PARTITION_NAME partition_name,
ddf.file_name file_name,
trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes
from dba_extents de
join dba_data_files ddf on  de.file_id=ddf.file_id
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
where 1=1
and ddf.file_id = 15
order by de.block_id desc
)
where rownum <= 100;

4. Rebuild Segments

    i. Tables

SQL>ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE TABLESPACE AA_DATA;

    ii. Lobsegments

SQL>select * from dba_lobs where owner='AD_MGR' and segment_name='SYS_LOB0000386007C00005$$';

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace); 
ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE LOB(THREAD_DUMP) STORE AS (TABLESPACE AA_DATA);

    iii. Index

SQL>ALTER INDEX AA_MGR.IDX$$_199120004 REBUILD ONLINE;

Clone PDB on 19c

1. Check Free space on server

SQL> col name for a10
col percentage 999.99
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

NAME          FREE_MB   TOTAL_MB PERCENTAGE
---------- ---------- ---------- ----------
TSTDB          129716    1351560 9.59750215

2. Check the size if exisitng PDBS on server

SQL> col name for a10
  select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;

    CON_ID NAME       OPEN_MODE  PDB_SIZE_GB
---------- ---------- ---------- -----------
         2 PDB$SEED   READ ONLY   2.29394531
         3 GISDEV     READ WRITE   189.53125
         4 GISDB      READ WRITE  160.301758
3 rows selected.

Clone on same server

1. Start Source PDB in readonly mode

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
GISDEV     READ WRITE
GISDB      READ WRITE

SQL> alter session set container=GISDEV;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------
/u01/oracle/oradata/cdb1/tstdb/system01.dbf
/u01/oracle/oradata/cdb1/tstdb/sysaux01.dbf

SQL> shutdown immediate;
Pluggable Database closed.

SQL> startup open read only
Pluggable Database opened.

2. Connect to container database and clone from source PDB

sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 12:32:58 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show con_name

CON_NAME
---------------------
CDB$ROOT

SQL> create pluggable database GISTEST from GISDEV FILE_NAME_CONVERT=('/u01/oracle/oradata/cdb1/tstdb','/u01/oracle/oradata/cdb1/tstdb1');

Pluggable database created.

3. Start the both Source and target PDB's

SQL> sho pdbs;

    CON_ID CON_NAME               OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GISDEV                         READ ONLY NO
         4 GISDB                          READ WRITE NO
         5 GISTEST						  READ ONLY NO

SQL> ALTER PLUGGABLE DATABASE GISDEV OPEN;
SQL> ALTER PLUGGABLE DATABASE GISTEST OPEN;

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GISDEV                         READ WRITE NO
         4 GISDB                          READ WRITE NO
         5 GISTEST						  READ WRITE NO

Clone from Remote server

1. Connect to Source PDB

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
GISPROD     READ WRITE
DBPROD     READ WRITE

SQL> alter session set container=GISPROD;

Session altered.

2. Create user and grant

create user clone_pdb identified by clone_pdb;

SQL> grant create session,create pluggable database to clone_pdb;

3. Create DB link on Target db pointing to source DB, You can use either way to create DB link

Method-1

SQL> CREATE DATABASE LINK prodDB 
    CONNECT TO clone_pdb IDENTIFIED BY clone_pdb
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=GISPROD))
            )';

Method-2

vi /u01/app/oracle/product/19.0.0.0/db_1/network/admin/tnsnames.ora
  ORCLPDB =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.example.com)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = GISPROD))
)

SQL> CREATE DATABASE LINK prodDB CONNECT TO clone_pdb IDENTIFIED BY clone_pdb using 'ORCLPDB';

4. Create pluggable DB using DB link

SQL> create pluggable database orclpdbnew from  orclpdb@dblink file_name_convert='/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/ oracle/oradata/orcl/orclpdbnew/');

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

There are many ways to overcome this issue

Method 1 - Increase parameter

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check current configuration of profile

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' 
and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- -------------------
APP01     DEFAULT             SESSIONS_PER_USER                500

3. Increase parameter value

SQL> ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 600;

Profile altered.

4. Makesure parameter has been changed successfully

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- --------------------
APP01     DEFAULT             SESSIONS_PER_USER                600

Method 2 - Kill Idle sessions based on duration

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Select Idle connection which are inactive more than 12 hours

set heading off;
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where username='APP01' and status = 'INACTIVE' and last_call_et/60/60 >= 12;

3. Kill Idle sessions from above output

alter system kill session '100,35175' immediate;
alter system kill session '1768,53954' immediate;
alter system kill session '2129,15490' immediate;
alter system kill session '2251,6956' immediate;
alter system kill session '3331,17248' immediate;
...............

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01

Method 3 - Kill sessions which create connection from particular machine/program

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check hosts which make multiple sessions

select machine,program,username,count(*)
from v$session 
group by machine,program,username
order by 4 desc;

machine  program  username  count(*)
-------  -------  --------  --------
e5a07b31a6e5	JDBC Thin Client	APP01	470
demo3021132.isaaviation.net	JDBC Thin Client	APP02	185

3. Kill from above output

begin
for s in(
    select *
    from v$session
    where machine = 'e5a07b31a6e5' and username = 'APP01'
)
loop
    begin
    execute immediate 'alter system kill session '''||s.sid||','||s.serial#||''' immediate';
    exception when others then
        null;
    end;
end loop;
end;

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01

Data Guard Broker Configuration in 19c

Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration

From Primary

1. Enable Broker configuration parameters

SQL> alter system set dg_broker_start=true;

SQL> alter system set local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.135)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_bos03)))';

2. Add entry for data guard broker on listener.ora

[oracle@bo3upsmsxodb01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxodb01.pearsoncmg.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = smsprod_bos03_DG)
      (ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = smsprod)
    )
  )

3. Reload listener to effective the changes

[oracle@bo3upsmsxodb01 ~]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:17

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
The command completed successfully

4. Makesure service for dataguard has been initiated

[oracle@bo3upsmsxodb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:22

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-JAN-2023 06:55:52
Uptime                    0 days 21 hr. 32 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /data01/app/oracle/diag/tnslsnr/bo3upsmsxodb01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "smsprodXDB" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_bos03" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_bos03_DG" has 1 instance(s).
  Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

From Standby

1. Enable Broker configuration parameters

SQL> alter system set dg_broker_start=true;

SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.38)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_stby)))';

2. Add entry for data guard broker on listener.ora

[oracle@bo3upsmsxdrs01 ahussmo]$ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxdrs01.pearsoncmg.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = smsprod_stby_DG)
      (ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = smsprod)
    )
  )

3. Reload listener to effective the changes

[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:14

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
The command completed successfully

4. Makesure service for dataguard has been initiated

[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:18

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                20-JAN-2023 22:33:24
Uptime                    1 days 5 hr. 59 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /data01/app/oracle/diag/tnslsnr/bo3upsmsxdrs01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "smsprod_stby" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_stby_DG" has 1 instance(s).
  Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enable Data guard configuration

1. Login to data guard command line interface via sys user

[oracle@bo3upsmsxodb01 ~]$ dgmgrl sys/f1Nal#cak3

2. Create new configuration

DGMGRL> create configuration 'smsprod_bos03_DG_config' as primary database is 'smsprod_bos03' connect identifier is smsprod;
Configuration "smsprod_bos03_DG_config" created with primary database "smsprod_bos03"

3. Make sure primary configuration is available

DGMGRL> show configuration;

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

4. Reset 'LOG_ARCHIVE_DEST_n' to avoid 'ORA-16698' Errors

[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> sho parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby

SQL> alter system set LOG_ARCHIVE_DEST_2='';

5. Add standby database to dataguard broker

DGMGRL> Add database 'smsprod_stby' as connect identifier is smsprod_stby;
Database "smsprod_stby" added

6. Make sure both primary and standby configurations are available

DGMGRL> show configuration;

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database
    smsprod_stby  - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

7. Reset 'LOG_ARCHIVE_DEST_n' to valid value

[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby';

SQL> sho parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby

8. Enable configuration

DGMGRL> enable configuration;

Enabled.

9. Verify configuration status is 'SUCCESS'

DGMGRL> show configuration

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database
    smsprod_stby  - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 38 seconds ago)

Configure SSL on Oracle E-Business Suite 12.2 using Self-Signed Certificate

1. Creating wallet

1. Surce run edition environemnt variable

$ source EBSapps.env run

2. Navigate to s_web_ssl_directory/Apache, If not create the the directory

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs

mkdir Apache

3. Create an Auto-Login Wallet on Apache directory

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache

$ orapki wallet create -wallet ./ -auto_login_only

4. Create a Certificate Request

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache

$ orapki wallet add -wallet ./ -dn 'CN=devoracleebs@nsb.local' -asym_alg RSA -keysize 2048 -sign_alg sha256 -self_signed -validity 3650 -auto_login_only

2. Modify the Oracle HTTP Server Wallet

1. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>/keystores/default

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/keystores/default

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso  .

2. Copy wallet into $EBS_DOMAIN_HOME/opmn/<s_ohs_instance_loc>/<s_ohs_component>/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/user_projects/domains/EBS_domain_DEVEGL/opmn/EBS_web_DEVEGL_OHS1/EBS_web_DEVEGL/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

3. Copy wallet into $EBS_DOMAIN_HOME/opmn/<s_ohs_instance_loc>/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/user_projects/domains/EBS_domain_DEVEGL/opmn/EBS_web_DEVEGL_OHS1/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

4. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>/proxy-wallet

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/proxy-wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

5. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OPMN/opmn/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

3. Configure Protocol and Cipher Suite for FMW Internal Communication

1. shutdown all services

./adstpall apps/apps

2. Edit opmn.xml from $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OPMN/opmn

vi /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/opmn.xml

change
<ssl enabled="true"
     wallet-file="<path to the wallet file>"/>

to
<ssl enabled="true"
    wallet-file="/finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/wallet" ssl-versions="TLSv1.0,TLSv1.1,TLSv1.2"
ssl-ciphers="SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA"/>

3. Edit admin.conf from $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>

vi /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/admin.conf

change
SSLCipherSuite SSL_RSA_WITH_RC4_128_SHA
SSLProtocol nzos_Version_1_0 nzos_Version_3_0

to
SSLCipherSuite HIGH:MEDIUM
SSLProtocol nzos_Version_1_0 nzos_Version_1_1 nzos_Version_1_2

4. Update the Context File and Config Files

1. Start Application services

cd $ADMIN_SCRIPT_HOME
./adstrtall apps/apps

2. Login to Fusion Middlewere EM console via http://<host_name>:<port>/em

http://192.168.133.10:7021/em

3. Select web tier target under the EBS domain

4. Navigate to Administration, then Advanced Configuration

   

5. Select ssl.conf file for edit

6. Update the Listen and the VirtualHost default port as follows

change
Listen 4463 to Listen 4443
VirtualHost _default_:4463

to
Listen 4443
VirtualHost _default_:4443

change
SSLProtocol    -all +TLSv1 +SSLv3
SSLCipherSuite HIGH:MEDIUM:!aNULL:+SHA1:+MD5:+HIGH:+MEDIUM

to
SSLProtocol    nzos_Version_1_0 nzos_Version_1_1 nzos_Version_1_2
SSLCipherSuite HIGH:MEDIUM

7. Click on Apply

8. Run following command to propagate the changes made through the FMW console to the context file

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
Enter the APPS user password:
Enter the WebLogic AdminServer password:
./adstrtall apps/apps

Review the adSyncContext.log for the changes that have been picked up and made to the context file

9. Edit Application contex file as per below

$vi $CONTEXT_FILE

change
<url_protocol oa_var="s_url_protocol">http</url_protocol>

to
<url_protocol oa_var="s_url_protocol">https</url_protocol>

change
<local_url_protocol oa_var="s_local_url_protocol">http</local_url_protocol>

to
<local_url_protocol oa_var="s_local_url_protocol">https</local_url_protocol>

change
<webentryurlprotocol oa_var="s_webentryurlprotocol">http</webentryurlprotocol>

to
<webentryurlprotocol oa_var="s_webentryurlprotocol">https</webentryurlprotocol>

change
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">8020</activewebport>

to
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">4443</activewebport>

change
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4443</web_ssl_port>

to
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4443</web_ssl_port>

change
<httpslistenparameter oa_var="s_https_listen_parameter">4443</httpslistenparameter>

to
<httpslistenparameter oa_var="s_https_listen_parameter">4443</httpslistenparameter>

change
<login_page oa_var="s_login_page">http://devoracleebs.nsb.local:8020/OA_HTML/AppsLogin</login_page>

to
<login_page oa_var="s_login_page">https://devoracleebs.nsb.local:4443/OA_HTML/AppsLogin</login_page>

change
<externURL oa_var="s_external_url">http://devoracleebs.nsb.local:8020</externURL>

to
<externURL oa_var="s_external_url">https://devoracleebs.nsb.local:4443</externURL>

5. Run Autoconfig on application tier

cd $ADMIN_SCRIPT_HOME
adautocfg.sh

6. Re-start Application tier services

cd $ADMIN_SCRIPT_HOME
./adstpall.sh apps/apps
./adstrtal.sh apps/apps

7. Access the system via following URL

https://devoracleebs.nsb.local:4443/OA_HTML/AppsLocalLogin.jsp

Enable TLS for WLS AdminServer 1. Setup a WebLogic Server Identity Keystore

1. Surce run edition environemnt variable

$ source EBSapps.env run

2. set an alias for orapki in order to pickup the executable from the $FMW_HOME and not the one under the 10.1.2 home

$ alias orapki=$FMW_HOME/oracle_common/bin/orapki

3. Create following directories

mkdir /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts -p

4. Copy wallet file into newly created directory

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

5. Copy following to newly created directory

cp /finsys/DEVEGL/fs1/EBSapps/comn/util/jdk64/jre/lib/security/cacerts /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

2. Convert the Oracle Wallet to a JKS Keystore

1. Change directory to wlsSSLArtifacts

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

2. Run the orapki command

orapki wallet pkcs12_to_jks -wallet ./ -jksKeyStoreLoc ./ewallet.jks -jksKeyStorepwd Ebs#1234

3. The ewallet.jks file will be generate. Extract alias using following command and note down the alias

keytool -list -keystore ewallet.jks -v

3. Configure SSL on WLS

1. Take a backup of $EBS_DOMAIN_HOME/config/config.xml file

2. Use the adstpall.sh script to stop everything on the run file system and start only admin server

cd $ADMIN_SCRIPT_HOME
adstpall.sh
./adadminsrvctl.sh start

3. login to the admin console via http://192.168.133.10:7021/console

4. In the WebLogic Server Administration console, under the Domain Configuration, click on Environment and Servers

5. Click Lock & Edit

6. Click on the AdminServer to configure

7. Under the Configuration tab, click on the Keystores sub-tab

8. Click Change next to the Keystores setting

9. Select the Custom Identity and Custom Trust option and click Save

10. Enter the identity details

For example:
Custom Identity Keystore: /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts/ewallet.jks
Custom Identity Keystore Type: JKS (This must be in uppercase.)
Custom Identity Keystore Passphrase: This must match the password used from the orapki command previously in Step 2. Ebs#1234
Confirm Custom Identity Keystore Passphrase

11. Enter the trust information

For example:
Custom Trust Keystore: /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts/cacerts
Custom Trust Keystore Type: JKS
Custom Trust Keystore Passphrase: Enter the cacerts keystore password. See The cacerts Certificates File, keytool. initial password is changeit
Confirm Custom Trust Keystore Passphrase: Confirm the cacerts keystore password

12. Click Save

13. Click the SSL tab

14. Enter the identity details

For example:
Private Key Alias: orakey. This would correspond to the alias extracted from the keystore previously in Step 2.
Private Key Passphrase: This must match the password used from the orapki command previously in Step 2. Ebs#1234
Confirm Private Key Passphrase

15. Click Save

16. Click the General tab

17. Select SSL Listen Port Enabled check box

18. Enter the SSL Listen Port

Note: The SSL Listen Port base values are available through the context variable s_wls_admin_sslport. Based on the server type, you need to choose the corresponding port value for the SSL Listen Port. You need to manually calculate SSL Listen Port value. For simplicity, the default SSL Listen port value is 1 prefixed with the server default Non SSL Listen port value.
For example, for port pool 0, the AdminServer Non SSL Listen port is 7001, so the AdminServer SSL Listen port will be 17001. 17021

19. Click Save

20. Select the SSL tab

21. Select the Advanced option and then perform the following

Set the Hostname Verification to Custom Hostname Verifier and the Custom Hostname Verifier field to weblogic.security.utils.SSLWLSWildcardHostnameVerifier

22. Click Save

23. Click Activate Changes

Post-Configuration Tasks

1. Restart everything including the Admin and Managed Servers using the adstpall.sh and adstrtal.sh

./adstpall.sh
./adstrtal.sh

2. Sync Changes to the Context File

perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

following changes will be reflect in contex file.
s_custom_trustKeyStoreFile - complete path of trust keystore
s_wls_admin_sslEnabled - true
s_wls_admin_sslport - AdminServer SSL port.
In the case where these listed context variables are not already populated, restart the middle tier services and re-run the following command:

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

Change Backup Retention Policy on Standby Database

Problem

Following error occured when try to change backup policies on standby db

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 days;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 09/08/2022 08:08:11
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file;

Solution

From Primary

1. Change backup polices as per the requirement

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 days;

2. Create standby controlfile and copy to standby database

SQL> alter database create standby controlfile as '/tmp/stby.ctl';
Database altered.

From Standby

1. Cancel redo apply

SQL> alter database recover managed standby database cancel;

2. Backup exisitng control files and replace newly copied controlfiles from primary

cd /u02/oradata/PROD_STBY/controlfile
mv control01.ctl control01.ctl_bkp
mv control02.ctl control02.ctl_bkp
cp /tmp/stby.ctl control01.ctl
cp /tmp/stby.ctl control02.ctl

3. Mount standby database

SQL> alter database mount;

4. Change standby file managemnet to manual inorder change datafiles

alter system set standby_file_management='MANUAL';

5. Rename the datafile names to same as standby datafiles

SQL> select name from v$datafile;
NAME
--------------------------------------
/u02/oradata/PROD/datafile/system01.dbf
/u02/oradata/PROD/datafile/syaux01.dbf
/u02/oradata/PROD/datafile/undo01.dbf
/u02/oradata/PROD/datafile/users01.dbf
/u02/oradata/PROD/datafile/data01.dbf
/u02/oradata/PROD/datafile/index01.dbf

alter database rename file '/u02/oradata/PROD/datafile/system01.dbf' to '/u02/oradata/PROD_STBY/datafile/system01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/syaux01.dbf' to '/u02/oradata/PROD_STBY/datafile/syaux01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/undo01.dbf' to '/u02/oradata/PROD_STBY/datafile/undo01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/users01.dbf' to '/u02/oradata/PROD_STBY/datafile/users01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/data01.dbf' to '/u02/oradata/PROD_STBY/datafile/data01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/index01.dbf' to '/u02/oradata/PROD_STBY/datafile/index01.dbf';

6. Change back standby file managemnet to auto

alter system set standby_file_management='AUTO';

7. Start redo apply

SQL> alter database recover managed standby database disconnect from session;

ORA-30554: function-based index is disabled

Problem

Following error occured from DB when access the table

Rejected - Error on table GPS.MAIN_PROD.
ORA-30554: function-based index GPS.FN_IDX_MAIN_PROD_1 is disabled

Solution

Check the table and index status

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'MAIN_PROD';

OBJECT_NAME  OBJECT_TYPE  OWNER   STATUS
-----------  -----------  ------- ---------
MAIN_PROD      TABLE       GPS     VALID

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'FN_IDX_MAIN_PROD_1';

OBJECT_NAME         OBJECT_TYPE  OWNER  STATUS
------------       ------------ ------ -------
FN_IDX_MAIN_PROD_1    INDEX      GPS     VALID

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     DISABLED

Re-build index which was disabled

SQL> alter index gps.FN_IDX_MAIN_PROD_1 rebuild;

Index altered.

Check the index status again confirm whether issue is sorted

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     ENABLED