Auto Extend Tablespace scripts

1 - A script for summation of datafiles in tablespaces, ie a tablespace space usage script.

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

2- A script that can give accurate tablespace usage statistics that include auto extend. It determines the space usage WITH auto-extend has taken into account, not just current free space.

select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_data_files a,V$DATAFILE b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID
union
select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_temp_files a,V$tempfile b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID;

Reference: Auto Extend Tablespace scripts (Doc ID 1908578.1)

APP-FND-01564: ORACLE error 1455 in afpodbinit

 The issue started after cloning the Oracle Database from an earlier release of 12.1.0.2 to 12.1.0.2 [ODA to ODA]

This issue was caused by the newly enabled Unified Auditing feature in the target database server.

Error:

Cause: afpodbinit failed due to ORA-01455: converting column overflows integer datatype.

The SQL statement being executed at the time of the error was: begin fnd_global.bless_next_init('FND_PERMIT_0000'); fnd_global.initialize(:session_id, :user_id, :resp_id, :resp_appl_id, :security_group_id, :site_id, :login_id, :conc_login_id, :prog_appl_id, :conc_program_id, :conc_request_id, :conc_priority_request, :form_id, :form_application_id, :conc_process_id, :conc_queue_id, :queue_appl_id, :server_id); fnd_profile.put('ORG_ID', :org_id); fnd_profile.put('MFG_ORGANIZATION_ID', :mfg_org_id); fnd_profile.put('MFG_CHART_OF_ACCOUNTS_ID', :coa); fnd_profile.put('APPS_MAINTENANCE_MODE', :amm); end; and was executed from the file &ERRFILE.

Fix:

With Oracle Database 12c, Unified Auditing is introduced. Prior to Oracle Database 12c, auditing is configured using the AUDIT_TRAIL database parameter, 

which is called traditional auditing. Unified Auditing is not certified with E-Business Suite. Bug 25540183 is logged for this Enhancement Request.

To disable the new Unified Auditing functionality:

1. Take the Apps and DB tier offline

2. Source RDBMS environment

3. cd $RDBMS_ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

4. Verify the output of the following query is FALSE

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

5. Take the DB and Apps tier online

Reference: APP-FND-01564 AND ORA-1455 Errors In Afpodbinit When A EBS Form Is Called Or Responsibility Selected (Doc ID 1963969.1)

RMAN Duplicate Fails ORA-38862: Flashback Database Logs Are In Use

 RMAN backup restore is failing with the following error when the flashback is turned on in the source database.

Error:
Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 14894
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 04/04/2021 23:37:08
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter database flashback off
RMAN-11001: Oracle Error:
ORA-38862: Flashback database logs are in use.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/04/2021 23:37:21
RMAN-05501: aborting duplication of target database
Recovery Manager complete.

Fix:

Update the below values on the target database and retry the clone.

SQL> alter system set db_flashback_retention_target=0 scope=both;
SQL> alter system set db_recovery_file_dest='' scope=both;

Reference  -  (Doc ID 2418803.1)