Oracle Apps DBA

Friday, June 18, 2021

Tablespace alerts

To check the tablespace in GB

select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from

(select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space

from dba_free_space group by tablespace_name UNION

select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from dba_temp_free_space) a,

(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size

from dba_data_files group by tablespace_name UNION

select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size

from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

Output

TABLESPACE_NAME SIZEGB FREEGB

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

APEX 6 1.8

APPS_TS_ARCHIVE 89 24.8

APPS_TS_INTERFACE 101.742188 38.3

APPS_TS_MEDIA 1025 54

To check the tablespace in MB

SELECT df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"

FROM

(SELECT tablespace_name,

ROUND(SUM(bytes) / 1048576) TotalSpace

FROM dba_data_files

GROUP BY tablespace_name

) df,

(SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,

2 3 4 tablespace_name

FROM dba_segments

GROUP BY tablespace_name

) tu

WHERE df.tablespace_name = tu.tablespace_name;

Output

Tablespace Used MB Free MB Total MB % Free

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

APEX 4351 1793 6144 29

APPS_TS_ARCHIVE 65696 25440 91136 28

APPS_TS_INTERFACE 64938 39246 104184 38

APPS_TS_MEDIA 994267 55333 1049600 5

APPS_TS_NOLOGGING 18935 727661 746596 97

Checking datafile location

set lines 1000

set pages 1000

col file_name for a60

col tablespace_name for a25

col creation_time for a20

select ddf.file_id, ddf.file_name, ddf.tablespace_name, to_char(df.CREATION_TIME,'dd-MON-yyyy hh24:mi:ss') creation_time,ddf.bytes/1024/1024/1024 "Size GB"

from dba_data_files ddf, v$datafile df

where ddf.file_id=df.file#

and tablespace_name='ranesh_DI_DATA'

order by df.creation_time;

Output

FILE_ID FILE_NAME TABLESPACE_NAME CREATION_TIME Size GB

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

40 datafile/xxecs_dist_data01.dbf ranesh_DI_DATA 06-SEP-2007 19:03:23 30

65 /datafile/xxecs_dist_data02.dbf ranesh_DI_DATA 27-APR-2008 23:57:36 30

68 datafile/xxecs_dist_data03.dbf ranesh_DI_DATA 11-AUG-2008 13:31:06 30

Check the size of particular tablespace.

set linesize 400;

set lines 200

col "Name" format a20

col GB for 9999999

col file_name for a100

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",

round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from

(select tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files

where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) a,

(select tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space

where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) b

Where a.tbl=b.tblsp;

Output

Name Total Size Free Space Pct Used Pct Free

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

ranesh_DI_DATA 3608.07031 24.3586426 99 1

Saturday, May 15, 2021

I need to bounce ,status,changes of each Concurrent Manager

The status of the concurrent manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

Deactivating the concurrent manager:When you deactivate a manager all requests (concurrent programs) currently running are allowed to complete before the manager(s) shut down.

SQL>update fnd_concurrent_queues set control_code='D' where concurrent_queue_name=‘Standard Manager’;

Set max and target processes to 0

SQL>update fnd_concurrent_queues set running_processes=0,MAX_PROCESSES=0 where concurrent_queue_name=‘Standard Manager’;

commit;

see the status of the Manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

Activating the concurrent manager:

SQL>update fnd_concurrent_queues set control_code='R' where concurrent_queue_name=‘Standard Manager’;

commit;

See the status of the Manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

SQL to find Pending Concurrent Requests & Status of Conncurrent Request

Count of pending concurrent requests:

select COUNT (distinct cwr.request_id) Peding_Requests FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'

AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name

in ( select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl);

Pending with CRM

select count(cwr.request_id) "Pending with CRM" FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu

WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE

AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'

AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Conflict Resolution Manager');

Pending with Standard Manager

select count(cwr.request_id) "pending with Standard Manager" FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu

WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE

AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'

AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Standard Manager');

Pending with Internal Manager

select COUNT (distinct cwr.request_id) "Pending with Internal Manager"   FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE    AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'     AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ('Internal Manager');

Pending with Any other Manager
this sql Prompts for the Manager Name

select COUNT (distinct cwr.request_id) Peding_Requests FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE    AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'     AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ('&Concurrent_Manager_Name');

User Concurrent Queue Status

select MAX_PROCESSES,RUNNING_PROCESSES from apps.fnd_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME

in(select CONCURRENT_QUEUE_NAME from apps.fnd_CONCURRENT_QUEUES_TL where USER_CONCURRENT_QUEUE_NAME='&User_Concurrent_Queue_name');

Concurrent queue name and its user concurrent queue name

col CONCURRENT_QUEUE_NAME for a30

col USER_CONCURRENT_QUEUE_NAME for a60

set line 200

select UNIQUE FCQ.CONCURRENT_QUEUE_NAME,FCQT.USER_CONCURRENT_QUEUE_NAME,fcq.max_processes,fcq.running_processes

from apps.fnd_concurrent_queues fcq,apps.fnd_concurrent_queues_tl fcqt where fcq.concurrent_queue_id=fcqt.concurrent_queue_id and fcq.application_id=fcqt.application_id order by fcq.running_processes desc;

Councurrent–>Manager–>Administer Screen
from back-end

select cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes,count(cwr.request_id)

FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu,apps.fnd_concurrent_queues fcq

WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE

AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'

and fcq.concurrent_queue_id=cq.concurrent_queue_id and fcq.application_id=cq.application_id

AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name

in (select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl)

group by cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes;

Thursday, April 15, 2021

Block Change Tracking

Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.

You can enable block change tracking when the database is either open or mounted. This section assumes that you intend to create the block change tracking file as an Oracle Managed File in the database area, which is where the database maintains active database files such as data files, control files, and online redo log files.

To determine if block change tracking is enabled, check the STATUS and FILENAME columns in the V$BLOCK_CHANGE_TRACKING view, using the following statement from the SQL or RMAN prompt:

SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

Enabling and Disabling Oracle Change Tracking file

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

We can also create the change tracking file in a desired location, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

Checking Whether Change Tracking is enabled

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not.

SQL> select status from V$BLOCK_CHANGE_TRACKING;

ENABLED => block change tracking is enabled.

DISABLED => block change tracking is disabled.

Query V$BLOCK_CHANGE_TRACKING to display the filename.

SQL> select filename from V$BLOCK_CHANGE_TRACKING;

Moving the Change Tracking File in Oracle

If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location.

1. If necessary, determine the current name of the change tracking file:

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

/u01/app/rman/rman_change_track.f

2. Shutdown the database.

SQL> SHUTDOWN IMMEDIATE

3. Using host operating system commands, move the change tracking file to its new location.

$ mv /u02/rman/rman_change_track.f /u01/app/rman/rman_change_track.f

4. Mount the database and move the change tracking file to a location that has more space. For example:

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RENAME FILE '/u01/app/rman/rman_change_track.f' TO '/u01/app/rman/rman_change_track.f';

5. Open the database.

SQL> ALTER DATABASE OPEN;

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

/u01/app/rman/rman_change_track.f

If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/rman/rman_change_track.f';

If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.

Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.

Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. The following two factors that may cause the file to be larger than this estimate suggests:

· To avoid overhead of allocating space as database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB increments. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

· For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;

Difference between dbtechstack,dbtier and dbconfig

When running adcfgclone on database node we had three modes in which it can be executed.

perl adcfgclone.pl dbTier

It will configure the ORACLE_HOME on the target database tier node and recreate the controlfiles. This is Used for cold backup.

perl adcfgclone.pl dbTechStack

It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.

This is to clone the database Separetely Using Rman hot backup

The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]
Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform,
or 'dll' for Windows.

perl adcfgclone.pl dbconfig

It is used to configure the database with context file.Database should be in open mode.

cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig target_context_file
Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/target_context_file.xml

Friday, April 9, 2021

ORA-01157 and ORA-01110

ORA-01157 is raised when Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like
- Datafile is deleted or corrupt
- Datafile is renamed or moved
- Mount point is incorrect
- Issues with Read/write permission on Datafile

To solve ORA-01157 we can use one of the following steps:
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recoved it by using a valid backup.
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not containt important segments, that can be dropped offline
- If datafile is renamed or moved then we need to get it in its original position
- If Mount point is incorrect, simply recreate the mount point
- if it is due to permission then we need to grant the permission at OS level

I faced ORA-01157 at my local environment, when I was restoring a cold backup. Fortunatially it was with a tablespace which was added for testing purpose and was not critical. So I simply drop the datafile using "OFFLINE DROP" clause and opened the database.

SQL> select tablespace_name,status,contents from dba_tablespaces;

select tablespace_name,status,contents from dba_tablespaces

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/u01/app/oracle/oradata/RMAN/datafile/apex01.dbf'

..............................................................................................................................

Solution

SQL> select NAME from v$datafile where file#=2;

NAME

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

/u01/app/oracle/oradata/RMAN/datafile/apex01.dbf

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;

Database altered.